V Lookup for data based on 2 conditions at once

[Thedaivisch]

Hi there,

I'm trying to create a Vlookup to display data in a grid by finding the value in tab 2 based on both the P/N and location at once and displaying it in the value column on tab 1. So that would be if row in tab 2 contains both specified PN AND Location, display value in tab 1. Possible? See below for what I mean.

Vlookupdiagram.jpg

[ChemistB]

You'll need to use INDEX/MATCH

=INDEX(Data table you want to return from not including headers, Match to find row of table, Match to find Column of Data)

If your data is in Sheet2!B2:D4

In Sheet1 C2
=INDEX(Sheet2!$B$2:$D$4, MATCH(A2, Sheet2!$A$2:$A$4,0), MATCH(B2, Sheet2!$B$1:$D$1,0))
copied down
Is that what you are looking for?

[Thedaivisch]

That command will match across set rows and columns, and therefore assumes the rows are the same data in the same order, right? (my diagram was crappy and a little misleading in that sense). Unfortunately, my data is a complete list of all the items in alphabetical order in sheet 2, and a random subset of the items, in a random order in sheet 1. Therefore the formula needs find item type and location in sheet one and then find a row with a match on both item type and location on sheet 2.

[Thedaivisch]

hmmmm...actually maybe that is right then...your formula doesn't seem to be working, so I can't tell. Is there a mistake in your example?