asked on

Need to do a VLOOKUP function based on 2 cells instead of just 1

I have 2 worksheets im needing to combine info on

I usually just use a VLOOKUP function to bring over the info i want. BUT in this case i have 2 cells i need to check before it brings the info over.

This is the function i normally use.

=VLOOKUP(A2,Sheet1!A:J,4,FALSE)

Like i said, in this case i need the vlookup to check 2 cells now.

1st cell i am checking is my "ReturnID"
I have some that have duplicate "ReturnID"
So i need to then check "LineNumber" then return the value i need.

Any ideas?

Anastasia D. Gavanas

a couple of ideas...
(1) a nested vlookup probably would do the trick
something like if(iserror(vlookup(condition1 for ReturnID);result 1;result 2) and one of the two results would be the second vlookup
(2) an OR or AND within the vlookup condition could return what you want

Could you provide with a small sample like
A B C D
1
2
3

and what the expected result would be?

Addie Baker

ASKER

Attached is an example

I need to match the (Return ID) from sheet 1 THEN the (Line Number) from sheet 1 THEN bring over the Net price to sheet 2

Make sense?

Addie Baker

ASKER

Didnt see the attached.. here's the link

http://dl.dropbox.com/u/5012364/Book1.xls

Anastasia D. Gavanas

and the result would be in Sheet 2 I assume
Could you please provide an example?
Like, look for ReturnID in Row 1 Sheet 2 in Data from Sheet1, if this is found, then lookup up LineNumber in Row1 Sheet 2 in Data from Sheet 2 and if that is also the same, bring over the Net price to Sheet 2 (correct?)

Addie Baker

ASKER

Yes that is correct.

Match the Return ID from sheet1 first, then match the linenumber from sheet1, then bring over the net price to sheet2

Anastasia D. Gavanas

The second argument is not really the LineNumber now is it, but should be the Item code I assume. Unless you mean to get the line number of the Return ID in the first sheet and match it to the row number where the ReturnID in Sheet2 is...

Addie Baker

ASKER

Well i have some item numbers that will be duplicates. The line number is the only other cell that is unique

Addie Baker

ASKER

The end result i need is to bring the (net price) from sheet1 and match it to the correct row on sheet2

SOLUTION

Anastasia D. Gavanas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anastasia D. Gavanas

Hope this works...will be back online in a couple of hours.

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wshark83

another way is to create a column with return id and line number (i.e. =concatenate(return id, line number)) in sheet which contains Net Price as the first column and then just do a vlookup i.e. (=vlookup(concatenate(return id, line number),Sheet1!A:F,6,FALSE)

Addie Baker

ASKER

redmondb, I used the yellow and it did exactly what i needed it to.

xtermie, yours worked, but i couldn't make it work with all the other data i had. Couldn't post the entire worksheet due to sensitive data, and couldn't manipulate your formula to make it work.

redmondb

Thanks, bignadad.

(I think the issue with the approach in sample-Book1.xls is that it needs to consider the two fields jointly not serially.)