# VLOOKUP - what am I doing wrong

1. ## 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)

2. ## Re: VLOOKUP - what am I doing wrong

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.).

3. ## Re: VLOOKUP - what am I doing wrong

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)

4. ## Re: VLOOKUP - what am I doing wrong

Originally Posted by Sambo kid
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)
The item I want returned is in column a, but I need to be searching in columns b-f for a match to what is in sheet column a.

5. ## Re: VLOOKUP - what am I doing wrong

Are you looking to find GS-214619 in col B of Sheet2 and pull the corresponding value from col A in the same sheet?

6. ## Re: VLOOKUP - what am I doing wrong

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.

7. ## Re: VLOOKUP - what am I doing wrong

I want to pull column a of Sheet2 into column b of sheet 1.

8. ## Re: VLOOKUP - what am I doing wrong

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...

9. ## Re: VLOOKUP - what am I doing wrong

That looks a lot more complicated than what I came up with. I'll let you know how well it works when I get to work. Thanks.

10. ## Re: VLOOKUP - what am I doing wrong

Originally Posted by :) Sixthsense :)
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...
This is almost working. I am getting correct results when a correct result exists, but when a result doesn't exist (as many of the entries don't have data), it is bringing back a random (it seems to be in sequential order) entry from sheet 2 column 1. If no correct entry exists, I want it to return a N/A or blank entry etc;

11. ## Re: VLOOKUP - what am I doing wrong

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...

12. ## Re: VLOOKUP - what am I doing wrong

``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))))

14. ## Re: VLOOKUP - what am I doing wrong

I have attached as version 2. Thank you!

15. ## Re: VLOOKUP - what am I doing wrong

I have attached as version 2. Thank you.

16. ## Re: VLOOKUP - what am I doing wrong

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...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1