Index Match Issue with internal concatenation

Hello all,

Fairly new using index match with excel. I've had success in the past, but it was a simpler formula. What I am trying to do is the following:

Using "BPO Open Order Report" column "C" + "D" to look up against "Warehouse Receipts Report" where column "B" + "C" will pull in the various fields i need. I think my issue with the formula is that I am trying to concatenate within the formula instead of creating a separate column with the concatenation.

The formula is in "BPO Open Order Report" in "O2", also posted here in case its a formatting issue.

=INDEX('Warehouse Reciepts Report'!B2:H7,MATCH(C2&D2,'Warehouse Reciepts Report'!B2&'Warehouse Reciepts Report'!C2,0),2)

Try this array* formula in cell O2 of the BPO Open Order sheet:

=INDEX('Warehouse Reciepts Report'!$H$2:$H$7,MATCH(1,(C2='Warehouse Reciepts Report'!$B$2:$B$7)*(D2='Warehouse Reciepts Report'!$C$2:$C$7),0))

* NOTE, that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Then you can copy this down as required.

Hope this helps.

Pete