Index Formula Using Multiple Criteria

Lacey C

I'm trying to create an index formula that matches two different columns in a separate sheet but I keep getting #INVALIDVALUE. What am I doing wrong?

=INDEX({02 - Pre-Con Tracker PRECON DATES}, MATCH(Comm@row, {02 - Pre-Con Tracker COMM}), MATCH(Lot@row, {02 - Pre-Con Tracker LOT NUMBER}))

Paul Newcome

Are you able to provide screenshots for context? How many columns does your first range cover? Are you trying to match on two separate columns or pull from two separate columns?

Lacey C

Paul,

I'm trying to match two separate columns.

I'm trying to pull the "Scheduled Date" into another sheet. But I need to match the "Comm" and "Lot" columns as well.

Lacey C

trying to add the precon date to this sheet, Paul.

Lacey C

@paulnewcome see above

Paul Newcome

In that case you will need an INDEX/COLLECT instead.

=INDEX(COLLECT({02 - Pre-Con Tracker PRECON DATES}, {02 - Pre-Con Tracker COMM}, Comm@row, {02 - Pre-Con Tracker LOT NUMBER}, Lot@row), 1)