CODEWITHSUNDEEP
excel
Mahmoud Tarief
Mahmoud Tarief

Reputation: 75

Index Match Concatenate for multiple criteria not working in excel

My index - match - CONCATENATE is not working if range array has one row only.

Not working

enter image description here

enter image description here

This is my function:

=INDEX(RETURN_TO_SUPP_TRX[COST],MATCH(CONCATENATE(RETURN_TO_SUPPLIER_HEADER[PO NUM],[@[ITEM CODE]]),CONCATENATE(RETURN_TO_SUPP_TRX[PO NUM],RETURN_TO_SUPP_TRX[ITEM CODE]),0))

I did cntrl+shift+enter but still same issue.

The issue happens if the lookup range for MATCH is 1 row. If it is more than 1 row it works fine.

I traced the issue and found that the match is not able to match although it is finding the lookup array as seen in the screen shot.

Any ideas?

Upvotes: 1

Views: 1596

Answers (1)

user4039065
user4039065

Reputation:

I don't actually spend a lot of time with structured tables and this buggy behavior is a main reason why. If you change the formula to include the headers in the RETURN_TO_SUPP_TRX table and add a Total Row, you can get past a single row not being recognized when two columns are concatenated.

Your array formula becomes,

=INDEX(RETURN_TO_SUPP_TRX[[#All],[cost]],MATCH(RETURN_TO_SUPPLIER_HEADER[po num]&[@[item code]],RETURN_TO_SUPP_TRX[[#All],[po num]]&RETURN_TO_SUPP_TRX[[#All],[item code]], 0))

You also need to go into Table Tools ► Design ► Table Style Options and add a Total row to the RETURN_TO_SUPP_TRX table.

Upvotes: 1

Related Questions