MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLookup Question


Posted by Chas on January 24, 2002 11:04 PM

I have 2 spreadsheets with similar data. On sheet 2, I want a VLOOKUP formula to search a column on sheet 1 and when it finds the exact match it returns the data in the same row as the match but 5 columns over. I can get it to return an "x" or any specific word, but not the contents of an adjacent cell because they are not on the same row #'s from sheet to sheet.
Any ideas out there?
Any help is greatly appreciated.


Posted by Stephen Hoadley on January 25, 2002 1:33 AM


Try This

=IF(ISERROR(VLOOKUP(D1,A$1:B$10,2,FALSE)),"XX",VLOOKUP(D1,A$1:B10,2,FALSE))

Posted by Aladin Akyurek on January 25, 2002 1:51 AM

I'll assume that you have 2 worksheets in the same workbook and A3:C10 houses the data in Sheet1 (with labels Number, Cost, and Del.Days)

I'll also assume that, in Sheet2, you have numbers in D from D3 on that you want to lookup in the data/table in Sheet1.

In G3 enter: =IF(COUNTIF(Sheet1!$A$3:$A$10,D3),VLOOKUP(D3,Sheet1!$A$3:$C$10,2,0),"XX")

==============

Posted by Chas on January 25, 2002 10:19 AM

Thank you Aladin,
That worked perfectly.(after I figured out how to adapt it to the real sheets) :)
Chas

Posted by Chas on January 25, 2002 10:22 AM

Thanks Stephen,
I couldn't get this to work correctly right away, Aladin had a version I could get to work but I shall play some more with your formula to see if I can get it to work.

Chas

Try This