Lookup non-ascending values in a column 3

[markappleby]

I have been attempting to look up a value (e.g. 4) from a column of numbers whose values are not in ascending order, in order to return a value from an adjacent column. The numbers are input into this column and can not be data sorted to be ascending. I know I can retrieve the value in the adjacent column by using the OFFSET and MATCH functions but was wondering if there was an easier way to do this.
I know LOOKUP, VLOOKUP and HLOOKUP all require ascending values.
Could anyone please help.

 

[SQLBI]

Hi,

by adding

,FALSE

to your

=VLOOKUP

it will not require sorting.

=VLOOKUP(Crieria,Range,Column,FALSE)

If you can post a little more info and a sample of your data we maybe able to assist further.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[markappleby]

Thanks Leigh,

I have a list of names of lorry drivers in A2:A24, which remains static, and the round number to which they are delivering is input into C2:C24. The round numbers will obviously vary numerically from 1 to 22. Another workbook will lookup which row in C2:C24 contains, for example 4, and I want the corresponding lorry drivers name to be returned to the workbook.
Your help is much appreciated.

 

[bluedragon2]

=INDEX(A2:C24,MATCH(<Your reference>,C2:C24,0),1)

<Your Reference> is the cell containing the value you want to look up.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SQLBI]

Right ok,

Try this, it seems to work with my sample data.

D11 was my Round number, drivers in Col B and Round in Col C.

=INDEX(B3:C8,MATCH(D11,C3:C8,0),1)

Hope this helps.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

I wish my browser would update when someone posts wilst i'm in mid draft.

Great minds etc Blue!


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[markappleby]

Thanks Leigh and Bluedragon2 - works perfectly.

Mark