How could I lookup for two column data and return the other column info?

Rachel Liang

For example, I want to look up lot number and QC test ID in the table below and return test results and final disposition ready?

Paul Newcome

Are your QC test ID's unique on each row? If so, then you shouldn't really need to also match on the Lot Number.

Rachel Liang

No neither the QC test ID nor the lot number is unique, but combination of both is unique.

Also I tried the simple INDEX MATCH formula for search one column and return one column, but still got "NOMATCH" error even though I know for sure it should has a match.

Formula as below: =INDEX({Over all tracker Lot number column}, MATCH([Lot number]@row, {Overall tracker Result column}, 0))

Rachel Liang

for Index Match function, do I use column only or a large range for the first range and second range? ) I only used columns for both ranges, should I expand?

Rachel Liang

ALso, it looks like I used vlookup function worked....

Paul Newcome

To match on two columns you are going to want to use an INDEX/COLLECT.

=INDEX(COLLECT({Test Result Column}, {QC Number Column}, [QC Number Column]@row, {Lot Number Column}, [Lot Number]@row), 1)