VLOOKUP question

[Raz]

hi, I have a table similar to the one below, but much longer. I would like
to lookup to do this. For 500 in column C, the function will look for number
greater than 500 in column A (this case its 569.3) and take the number beside
in column B (this case 86.09) to column D (in D1).
thanks in advance.


A B C D
71.162 5.55 500 86.09
142.325 15.12 100
213.487 25.2 200
284.65 35.88 300
355.813 47.24 400
426.975 59.36 500
498.138 72.31 600
569.3 86.09 700
640.462 100.68 800

 

[Shane Devenshire]

Hi,

One problems - what to do if more than one entry meets the given condition?
For the last entry that meets the condition you can use the following array
formula:

=INDEX(B1:B9,MAX(($A$1:$A$9>C1)*ROW(1:9)),0)

To be an array you must enter it by pressing Shift+Ctrl+Enter not Enter.

 

[Mike H]

Hi,

Try this in d1

=MIN(IF($A$1:$A$9>=C1,$B$1:$B$9))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Raz]

Thanks Shane,
Lets reword my prob.
For 500 in column C, the function will look for the number immidiately
greater than 500 (the first number which is greater or the smallest among
numbers larger than 500) in column A (this case its 569.3) and take the
number beside in column B (this case 86.09) to column D (in D1).

 

[Raz]

thanks it works,
but could this be done with VLOOKUP please ????

I will need to enter this in equations. i am not sure those curly brackets
would create problem in equation.

for example if I were to do this

=F2*G4*{=MIN(IF($A$1:$A$9>=C1,$B$1:$B$9))}*K25
etc.
would this work??????

 

[Mike H]

Hi,

Can it be done with Vlookup? No

would this work?

=F2*G4*{=MIN(IF($A$1:$A$9>=C1,$B$1:$B$9))}*K25

No

But this would
=F2*G4*MIN(IF($A$1:$A$9>=C1,$B$1:$B$9))*K25

Don't forget to follow the instructions for array entering formula

Mike