Can Lookup or match function return greater than value ?

[praveen_ce]

Hi ppl

I am facing this below problem from long time

I have table

[pre]

A---X----Y----Z
1---10---20---30
2---20---40---60
3---30---60---90

[/pre]
if input for A is 2.5 and i want output of x or y or z

then all built in formulas excel gives "less than value "

like if i put


=MATCH(A_value,A_list,1)


it gives as 2 not 3


I cant use greater than function as it gives error and same with lookup function

is there a way i can tackle this issue

normally i just add +1 to it n carry on with my work

 

[NARAYANK991]

Hi Praveen ,


Are you giving real-life data from your work , or is your data just sample data which you have made up ?


When you give examples , please choose realistic examples so that correct conclusions can be drawn , and solutions given. Otherwise , solutions will be provided for the data which has been posted , and later on , it will be found that these solutions do not work with actual data.


You have chosen an example such as 2.5 ; will your requirement be the same if the input for A is 2.1 ?


As it stands , can you not round up the input to get a value of 3 , which will then match ? Or is your real-life data quite different ?


Narayan

 

[praveen_ce]

Narayan thanks for d reply

this data is just made up stuff

i deal with all kind of inputs it can be anything 2.1, 2.22 or 2.22223

but it should take "greater than " value

i am using very crude method like

'=if(A_value>3,3,match(A_value,A_list,1)+1))'

 

[NARAYANK991]

Hi Praveen ,


Since you say your present data sample is just made up , can you upload your real-life data , at least with that column of data which will be used for matching , along with the input value which will have to be matched ?


I do not understand your formula :


=if(A_value>3,3,match(A_value,A_list,1)+1))


Suppose A_value is 1 ; is the formula supposed to return a match value of 2 ?


In fact , the MATCH function ( with the last parameter omitted ) will return 3 for any input value greater than or equal to 3 ; the check for A_value > 3 is not needed. In fact , a check for A_value less than 1 is needed.


Let us just ignore the MATCH function , preferably all Excel formulae , and concentrate on what you wish to do ; can you explain that ?


Narayan

 

[praveen_ce]

Narayan u r rite that formula doesnt work well.

i will give u a real life data

I am a piping engineer , we have a pipe standard to select for our calculation.

ex:

min dia required -12.1


Std. pipe dia

6.4

9.5

12.7

15.9

19.1

22.2

25.4

31.8

38.1

50.8

63.5

76.2


-now i have to select 12.7

'=IFERROR(MATCH(12.1,Pipelist,0),MATCH(12.1,pipelist,1)+1)'

this one works well but not foolproof

 

[NARAYANK991]

Hi Praveen ,


I can only repeat myself !



When you give examples , please choose realistic examples so that correct conclusions can be drawn , and solutions given. Otherwise , solutions will be provided for the data which has been posted , and later on , it will be found that these solutions do not work with actual data.




You have selected one example ; I do not know what can be concluded from this one example. Suppose the minimum pipe diameter required is 9.6 ; will you still select the next higher one of 12.7 ?


If so , your formula should work ; all you need is to add a check for the minimum value :


=INDEX(pipelist,IF(ISNA(MATCH(input_dia,pipelist,0)),IF(ISNA(MATCH(input_dia,pipelist)+1),1,MATCH(input_dia,pipelist)+1),MATCH(input_dia,pipelist,0)))


This will give an error if the input_dia exceeds the maximum value in the pipelist.


Narayan

 

[jeffreyweir]

How 'bout this:

=IFERROR(INDEX(d,MATCH(input_dia,d,0)),INDEX(d,1+MATCH(input_dia,d,1)))


Edit: Whoops, I see the Op has already posted this above.


Another way to check for min/max values is to use data validation on input_dia

 

[praveen_ce]

Hi narayan

your soln was wat i was hoping to get thank you.

and to clear ur doubt

yes narayan even if it is 9.6 mm , i should go for higher pipe dia.

otherwise it will fail.


thnks again

 

[Colin Legg]

Another option...


If you sort your pipelist so the numbers are descending:

[pre]

Std. Pie dia
76.2
63.5
50.8
etc...[/pre]

You can then use this simple formula:

[code]=INDEX(PipeList,MATCH(D2,PipeList,-1))

where D2 contains 12.1


The formula will return #N/A[/code] if D2 contains a value greater than the first one in the list, which is (I imagine) what you would want to happen in that scenario.

 

[shrivallabha]

You can also use [CTRL + SHIFT + ENTER]:

=MIN(IF(PipeList>=B1,PipeList))


This won't require the data to be sorted.


Edit: Oops, I see that OP has stated LOOKUP function. What I've posted isn't lookup.

 

[jeffreyweir]

Colin: Very elegant solution.

Shrivallabha: Ditto