Vlookup

[moni_tm]

Hi

please can you help, my Vlookup on this attached spreadsheet returns a #n/a when i'm sure it's right. (highlighted yellow, sheet 2)

Column E has a drop down Menu and I want Columns F & G to return the correct accounting codes from the Merbs Sheet.

Any help is greatly appreciated.

Tom

[Jonmo1]

Vlookup is Zoolander challenged.
It can only go 'To The Right'
It finds a match in the lefmost column of the range, and returns a value from the designated column 'To the right' of where the match is found.

You need Index/Match to do what you want.

Try this in F8
=INDEX(MERBS!B$9:B$162,MATCH($E8,MERBS!$D$9:$D$162,0))

[AliGW]

VLOOKUP works from left to right, not right to left. Try this in F8 on Sheet 2:

=INDEX(MERBS!$B$9:$B$162,MATCH(E8,MERBS!$D$9:$D$162,0))

[moni_tm]

Great Thanks that has worked, can you help with column G returning the GL Account??

[moni_tm]

Never mind, I've got it all to work thank you all!!

Tom

[Tony Valko]

It's possible to get VLOOKUP to work from right to left:

Data Range

	A	B	C	D	E
1	A	Justin Thyme		Anne Teak	C
2	B	Colin Allcars
3	C	Anne Teak
4	D	Ben Dover
5	E	Betty Diddent
6	------	------	------	------	------

This formula entered in E1:

=VLOOKUP(D1,CHOOSE({1,2},B1:B5,A1:A5),2,0)

However, a straight-forward INDEX/MATCH is probably a better option.

[Jonmo1]

You're welcome

[AliGW]

Glad to help!