LOOKUP problem with non-sorted vector

A

AMSH

Hey,

Can anybody tell me how to get around excel's requirement that the lookup
vector must have sorted data - if the data isn't sorted it returns an
incorrect value.

Example:
Column A contains: 1,2,3,4,5
Column B contains: 5 blank cells, 4,5,1,3,2
Column C contains: E,D,C,B,A

I want to lookup a cell in column B - B8 (1) in column A - A1 and return the
corresponding value in column C - C1.

Any ideas?

Many thanks,

AMSH
 
M

Mike H

Hi,

The range only needs to be sorted for nearest match. Try this

=VLOOKUP(B8,A1:C5,3,FALSE)

Mike
 
G

Gary''s Student

With A1 thru C10 containing:

1
2
3
4
5
6 4 E
7 5 D
8 1 C
9 3 B
10 2 A

=VLOOKUP(A1,B1:C10,2,FALSE) will display C

The FALSE is important!
 
B

Bernard Liengme

=IF(B1="","",INDEX($C$1:$C$5,MATCH(B1,$A$1:$A$5,FALSE)))
We test to see if the B cell is blank
If not we match B's value with the A vector, and then find corresponding
item in C vector

But since A is 1,2,3,4 we could simplify the last part to
=INDEX($C$1:$C$5,B1)
best wishes
 

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

Top