Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-04-12
14
730 Views
Last Modified: 2012-04-12
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?
0
Comment
Question by:bignadad
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 37837245
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?
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837271
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?
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837275
Didnt see the attached.. here's the link

http://dl.dropbox.com/u/5012364/Book1.xls
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 18

Expert Comment

by:xtermie
ID: 37837301
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?)
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837314
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
0
 
LVL 18

Expert Comment

by:xtermie
ID: 37837322
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...
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837329
Well i have some item numbers that will be duplicates. The line number is the only other cell that is unique
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837339
The end result i need is to bring the (net price) from sheet1 and match it to the correct row on sheet2
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 250 total points
ID: 37837353
Ok think I got it...
check attachment
Had to rearrange data in Sheet 1 to bring ReturnID first
sample-Book1.xls
0
 
LVL 18

Expert Comment

by:xtermie
ID: 37837358
Hope this works...will be back online in a couple of hours.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 37837374
Hi, bignadad.

The attached has two possible solutions...
(1) Yellow - add a new Lookup column (containing Return ID and LineNo.) to Sheet1. Match to this field. (Solution would be easier if Lookup column could be move to before Net Price column so a VLookup could be done.)
(2) Green - use an array-entered formula to find Net Price. (N.B. is there's any chance of duplicate entries then option 1 is safer as it will simply find the first Net Price, whereas this one will total the Net Prices.)

Regards,
Brian.Book1-V2.xls
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37837420
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)
0
 
LVL 2

Author Comment

by:bignadad
ID: 37837444
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37837601
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.)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question