VLOOKUP - what am I doing wrong

Please Login or Register  to view this content.

HTML Code: 

I keep using a VLOOKUP to reference the second sheet, and if the number from the first sheet is found in columns 2-5 of the second sheet, I want it to return to the first sheet a column with the number that corresponds in the first column on the second sheet. All I can ever make my function do is return to me the same number that is located in the first example directly next to it, ie:

Please Login or Register  to view this content.

This is what I have been using on "Sheet 1" : =VLOOKUP(A2,Sheet2!$B$2:$F$1001,1,FALSE)

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

for starters, your vlookup is only referencing one column, you are saying if the item you are looking up is in columns B through F, look for it only in col B - that is what the "1" is telling it.
=VLOOKUP(A6,Sheet2!$B$2:$F$1001,1,FALSE)

Now that I look at your spreadsheet and your "AFTER" worksheet, I'm more confused.
Are you looking to find GS-214619 in col B of Sheet2 and pull the corresponding value from col A in the same sheet?

we cross posted.
So based on that I think you need an index and match formula although there is a reverse vlookup but I don't think it is the best thing for your issue.
Problem is I'm not very good at using index and match so sixth sense can help you better.

In B2 Cell of Sheet1

=INDEX(Sheet2!$A$2:$A$1001,SUMPRODUCT(1*MAX((Sheet2!$B$2:$F$1001=A2)*ROW(Sheet2!$B$2:$F$1001))))

Drag it down...

No it won't get any error result I believe... Can you please show it in excel in which cell it is failing for better understanding...

Please Login or Register  to view this content.

In this example, the first five entries are correct. The sixth entry brings back 08QBE000033, but that entry is incorrect. GS-162791 does not exist in sheet2. Entries 7-9 are also incorrect. Entry 10 *IS* correct. I should get a N/A or blank entry next to 08QBE000033.

And I had to edit your code a bit, I used =INDEX(Sheet2!$A$1:$A$1002,SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002))))

Please show it in excel please........

Thanks for the attachment file try the revised formula

In B2 Cell

=IF(SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002))),INDEX(Sheet2!$A$1:$A$1002,SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002)))),"")

Drag it down...