Match multiple columns to bring back one result

I'm trying to expand a Vlookup by not comparing just one colomn of data in two different spreadsheets, but I need to EXACTLY match 3 columns of data.

I need to match: Manufacturer, Location and Item Number in both spreadsheets and then if they all match, I need to bring back the Invoice Amount from the second spreadsheet.

Thanks for any help!

To use VLOOKUP for this, the values searched would have to all be in one column in the far left column of the table. So insert a new blank column A and create an "index" in that column using something like this... in A2, then copied down:

=B2 & "-" & C2 & "-" & D2

Of course you'll have to match the correct columns to get the key to format properly as Manufacturer-Location-Item Number.

Then you can use a Vlookup in your other sheet normally. You'll use the same technique to create the first parameter lookup value:

=VLOOKUP(A2 & "-" & B2 & "-" & C2, Sheet2!$A:$Z, 4, 0)

The red parameter is the column that you'll get an answer back, adjust as needed.

The "match" part of an INDEX/MATCH is basically the same issue as with a VLOOKUP. Since your match values are in separate columns, I would create a new column in the data to do this match.

The benefit of an index/match is that you could put this new column out to the far right, or anywhere, actually. VLOOKUP requires the key column be on the left.

So, same idea, for an index match, create a key column off to the right somewhere, perhaps in AA2:

=B2 & "-" & C2 & "-" & D2

...then copied down the data set.

Then INDEX the column you want info from, and MATCH to the new column in AA:

=INDEX(Sheet2!E:E, MATCH(A2 & "-" & B2 & "-" & C2, Sheet2!$AA:$AA, 0))