Searching for multiple criteria in excel

Question

Am a little new to excel and puzzled as to how to solve this problem. I have two excel sheets.

Sheet 1 looks like (large data set):

HIC    GSN     ND11
H1C    00214   0212107
C4I    07287   0214380
L1A    07731   0214501

Sheet 2 looks like:

Condition    HIC1   HIC1    HIC1    GSN      GSN     GSN
 AMA         B60     B61    B62     02934   02935
 ALD         H1A     H1C            04821   03473
 HEC         W0A     W0B

For example, in Table 1, if the HIC or GSN on row 1 (H1C or 00214) is present anywhere in table 2, it fetches the ND11 number from table 1 corresponding to the match. I tried using a VLOOKUP, but am failing at how to go about approaching the problem.

The VLOOKUP I tried was,

=VLOOKUP(OR(Table1'H1C', Table1'00214),Table2A2:G2,Table1'ND11',0)

Any help would be great.

Answer 1

UPDATE:

OK, I re-read the question and I think I misunderstood; you want to match HIC or GSN. In that case, I had to use some helper columns because I don't know of an way to arithmetically OR arrays. So, I came up with this ...

... where I7, J7, and K7 formulas are (respectively):

=IFERROR(MATCH(B7,$A$2:$A$4,0),IFERROR(MATCH(C7,$A$2:$A$4,0),IFERROR(MATCH(D7,$A$2:$A$4,0),NA())))
=IFERROR(MATCH(E7,$B$2:$B$4,0),IFERROR(MATCH(F7,$B$2:$B$4,0),IFERROR(MATCH(G7,$B$2:$B$4,0),NA())))
=INDEX($C$2:$C$4,IFERROR(I7,IFERROR(J7,NA())))

You could replace the last IFERROR(J7,NA()) with just J7, but the Excel error handling will whine.

---

ORIGINAL:

If I understood your problem correctly (which I doubt), you want to get the ND11 value from Sheet1 for the matching HIC and GSN values. For single matching, the INDEX/MATCH combo works well. However, for multiple criteria, the SUMPRODUCT works better. In the following screenshot ...

... I used the following formula to get the value of ND11 in the first table only where the value from column C matches the HIC and column F matches the GSN ...

=SUMPRODUCT((--($A$2:$A$4=C10)*--($B$2:$B$4=F10))*$C$2:$C$4)

Now, I'm not sure why you have three HIC's and three GSN's, so you'll have to elaborate on your question if this isn't the expected result.