VLOOKUP question

R

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
 
S

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.
 
M

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
 
R

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).
 
R

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??????
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need an Equation 6
Vlookup or similar 7
Capture lines with entries? 21
If then request 1
sumproduct with unhidden data 1
Excel function 4
VLOOKUP 7
GET QTY in column J 14

Top