vlook up help request

[mattless1]

Hi,

I'm trying to complete a vlookup formula, but doesn't seem to be returning the correct info

I have two sheets.
Sheet one cell A1 is where i would like to have the Code returned from sheet two.

I've tried looking for the names, then returns the code against the name in sheet 1. but I'm doing something wrong. i have also tried the Match formula

This is the code I'm using =VLOOKUP(B2,Sheet2!$A$2:$B$4,1,0)

I'd appreciate it if someone could point me in the right direction.

sheet 1
A1 A2 A3
[TABLE="width: 274"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]ID [/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Paul Mension[/TD]
[TD]206[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]David Docks[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Debbie Stock[/TD]
[TD]471[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
A1 A2 A3
[TABLE="width: 245"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Paul Mension[/TD]
[TD]206[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]David Docks[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Debbie Stock[/TD]
[TD]471[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

Vlookup is about using the Left column as reference, now if you duplicated the 001 to 003 to the right of 206 471 and used that you can get the value

 

[gaz_chops]

Or use Index/Match

 

[mattless1]

Vlookup is about using the Left column as reference, now if you duplicated the 001 to 003 to the right of 206 471 and used that you can get the value

Thanks for help, i'll try it and let you know.

Many thanks

 

[mattless1]

Works perfect, thanks so much Never though of moving the columns. You learn something new everyday. Appreciate that.

 

[mole999]

as Gaz says INDEX MATCH is the way to do it, but sometimes its quicker to use what you know, unless you spend time learning a new skill

 

[mattless1]

Could explain a bit more on your way of doing things please. may learn another way.

 

[gaz_chops]

For Index/Match, try

=Index(Sheet2!$A$2:$A$4,match(B2,Sheet2!$B$2:$B$4,0))

Index the range that you want the result from.
Match is the range to look for the lookup value in.
The ,0 at the end of match says to look for an exact match.

hth

Gaz

 

[mole999]

I learnt it by understanding that MATCH can be written first to find out what ROW to return, then INDEX would use the row number for the result. You can use different ranges, but you have to accommodate for the offset