VLOOKUP in Multiple Columns - Is it Possible?

[RBusiness]

Hey folks,

Is it possible to do a vlookup in multiple columns and when there's eventually match, the same column index would apply therefore pulling in the respective data?
If so, how would you do that? I appreciate your help, this is the second gnarly one I had this week.

 

[Joe4]

Not really sure what you mean. I think it would be helpful to see a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RBusiness]

LIST 1	Values	LIST 2	Values
Widget	1111	Cridget	555
Slidget	2222	Snigget	666
Ridget	3333	Pigget	777
Fidget	4444	Blidget	888

Say I have maximum rows possible in excel and need additional columns and make two lists.
If I want to find the value for "pigget" can a do so with a VLOOK that Looks in List 1 AND List two and then when it finds a word or query, it moves over one cell and grabs the data?

 

[RBusiness]

Not really sure what you mean. I think it would be helpful to see a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I will certainly check that out.

 

[Joe4]

You could do two separate VLOOKUPS and combine them together, i.e. structured like this:

=IFERROR(VLOOKUP(...),"") & IFERROR(VLOOKUP(...),"")

Note that it will return an empty string for the one it does not find. So if there is exactly one match, it will return that match and the empty string sewn together.

However, if you have over a million rows of data and are trying to do lookups on them, I think you are probably using the wrong tool for the task.
What that would describe is a relational database model, and a database program like Microsoft Access, SQL, MySQL, or Oracle would probably handle this more gracefully than Excel, which will probably start having performances issues as the size of your data increases.

 

[RBusiness]

Thank you. I truly appreciate your help. I will certainly explore Access in the near future. I just love excel - so I try with it as much as I can.

 

[Joe4]

Thank you. I truly appreciate your help. I will certainly explore Access in the near future. I just love excel - so I try with it as much as I can.

You are welcome!

I love Excel too, but sometimes it isn't the best tool for the job!
When you get to large amounts of data and lookups across data sets, it will start to get clunky and slow.
The thing that many people get confused about is that Access is NOT an extension of Excel - it is a very different program and serves an entirely different purpose than Excel.
While you can "brute force" into using Excel like a Relational Database, it is not what it was designed for, so performance and ease of use take a hit.

 

[RBusiness]

Following up - this is working awesome. Thank you for your help.

 

[Joe4]

My pleasure!