Little excel help required

[doris_day]

I'm improving every day with my excel skills but need a little help with something that will probably be very easy for some of you.

Say I have 2 columns, A and B. Say there are prices in the A cells and these are ranked in the B cells. I need a formula that will point to the respective price if I know the rank. So it would read something like =if(rank=4,price=?,"")

Hopefully you'll get my drift but ask any questions if you don't

[johnnic]

The VLOOKUP function will do what you want.

Put your ranks in the first column and your prices in the second column. For example, if you have 10 ranks in cells A1 to A10 from 1 to 10 and 10 prices in cells B1 to B10, and let's assume you want to know the 5th rank price. The following formula will do the trick:

=VLOOKUP(5,A1:B10,2,FALSE), where

5 = the rank
A1:B10 = the range of the data including the rankings
2 = you want the data that exists in the second column of your range
FALSE = you want an exact match.

You can set the rank number in a separate cell and refer to it in your formula, of course.

Hope this helps. Please let me know if I haven't been clear.

John

[doris_day]

I forgot about VLOOKUP. However, it doesn't appear to work. Could it be that the rank column cells are based on the RANK function ?

[doris_day]

Found the problem. I needed the columns in a different order.
So, thanks for pointing me in the right direction.

[johnnic]

It shouldn't cause a problem. I just tried it on the following data:

Cell A1 =RANK(B1,$B$1:$B$4,1)
Cell A2 =RANK(B2,$B$1:$B$4,1)
Cell A3 =RANK(B3,$B$1:$B$4,1)
Cell A4 =RANK(B4,$B$1:$B$4,1)

Cells B1,B2,B3 and B4 = $34, $654, $87 and $543 respectively.

Cell C1 =VLOOKUP(2,A1:B4,2,FALSE)

The solution is $87, which is what you would expect.

I'm at a loss to understand what is going wrong.

John

[doris_day]

No, it works fine. It was me, not the formula.
Thanks again.

[osknows]

VLOOKUP relies on the lookup data being in order and the return value must be to the right of the lookup range

MATCH and INDEX functions can be used to get around the problems with VLOOKUP and are much faster over very large ranges

If Prices are in range A1:A10 and Rank in B1:B10

=MATCH(4,B1:B10,0) will return the row count from the start of the range which equals 4

=INDEX(A1:A10,MATCH(4,B1:B10,0),1) will return the value in column A

[doris_day]

Thanks osknows. Yes, I had my columns initially reversed, so I simply swapped them round but your new idea will be useful in the future. Thanks for that.