VLOOKUP between sheets

[Hammond]

OK, I have three sheets on an excel spreadsheet.

I want to lookup the value from sheet 3 column G; find it in column B of sheet 1.
I then want the values from sheet 1 of the columns A, B, C & D (where the match was found) to be copied to sheet 3.
Any idea how I do this please ?

I tried this : =VLOOKUP(G5,'Main spreadsheet'!B2:B204, 3, FALSE)
but it didn't work.

Thanks

[AliGW]

Please supply a sample workbook with expected outcomes.

[Hammond]

sheet 1.png

sheet 3.png

Sheet 1 and sheet 3 pics above, hope that helps.
The row on sheet 3 with 198 and the numbers and name are how I want it to look if possible.

[AliGW]

Now, here's the bad news. You can't use VLOOKUP to return something to the left (Col A in this case) of what you are looking up (Col B).

Yes, you can! Have a look here:

http://www.excelforum.com/excel-tips...d-vlookup.html

INDEX MATCH is easier, though.

[ChemistB]

You're asking Excel to return the third column of your range and then you give the range as B2:B204 (1 column). So if you want to return col D, it would be

=VLOOKUP(G5,'Main spreadsheet'!B2:D204, 3, FALSE)

Now, here's the bad news. You can't use VLOOKUP to return something to the left (Col A in this case) of what you are looking up (Col B). You will need to switch to INDEX/MATCH or move column B to A.

[Hammond]

Brilliant, many thanks, that works perfectly

[AliGW]

Can you not provide the workbook rather than expecting us to recreate it ourselves? Also, you need to show expected outcomes, not error messages.

[Hammond]

I did show expected outcomes, it's just I had the formula in there and got the error, so that's on the line below!

[AliGW]

I did show expected outcomes, it's just I had the formula in there and got the error, so that's on the line below!

Sorry! I thought they were column headings. However, next time, save time and attach a sample workbook instead.

Brilliant, many thanks, that works perfectly

Who are you thanking? I presume ChemistB?

[ChemistB]

@ AliGW,
Yes, I had seen that trick (although admittedly, I don't have it memorized since I never use it). I just wasn't about to try to explain it to a novice. That is a good thread link though. Thanks AliGW.