concatenated matches across two sheets

[kenneu]

Sorry - new and it won't let me attach anything so doing the best I can below for sample data. I'm looking for the shortest method to do a lookup using concatenated fields in a first sheet with concatenated fields in a second sheet. I have a list of serial numbers and customers in one sheet that contains various data. Customer can have several serial numbers so I concatenate to create a key field. In a second sheet, I also have a list of serial numbers and customers with a different set of corresponding data. I would like to have a vlookup with a concatenate in the vlookup as well as in the array but it appears that is impossible and I need to do something else. Assume it is something like below:

Sheet 1 Sheet2
A B C A B C D
Name Serial Number Model Concat Name Serial Number Series
Susy 12345 X1 Susy12345 Susy 12345 XRAY

So I want to look up a concat of A&B from Sheet1 using B&C of Sheet2 so I can add the Series to sheet 1. Currently I create a concat in Sheet2 in column A as above and then do vlookup(A&B,Sheet2!A:D,4,false). This gets me what I'm looking for but I have to go modify sheet 2 so my lookup will work. I would like to not have to do that and instead use a formula to concatenate the array as well. I'm assuming I'm either going to have to use indirect or some kind of index/match but I haven't been able to figure it out.

[alansidman]

Read the yellow banner at the top of this page on how to attach a file.