INDEX MATCH array formula error for table with single row

Question

I am working with INDEX MATCH formula with multiple criteria in a Excel Table. My array formula looks like

{=INDEX(Table1[Column3],MATCH(1,(Table1[Column1]=value1)*(Table1[Column2]=value2), 0))}

The formula works fine if table has more than one row. The formula fails and returns #N/A when table has 1 row. Is there any workaround for table with single row?

My Table is pulling data through database using power query. Hence number of rows are dynamic. Table is as follows

Column1  |  Column2  |  Column3
-------------------------------
value1   |  value2   |  value3

Can't replicate, works fine for me. What column are your trying to return? Column3? Then try: =INDEX(Table1[Column3],MATCH(1,INDEX((Table1[Column1]="Value1")*(Table1[Column2]="Value2"),),0)) — JvdV
– JvdV, Commented Jan 29, 2020 at 10:44
Thanks. This is what I was searching for. INDEX inside MATCH is what was required. Please add this comment as answer so that it can be useful. — Siddharth692
– Siddharth692, Commented Jan 30, 2020 at 9:13

JvdV · Accepted Answer · 2020-01-30 10:05:28Z

2

As per my comment, I couldn't replicate but I would recommend using:

=INDEX(Table1[Column3],MATCH(1,INDEX((Table1[Column1]="Value1")*(Table1[Column2]="Value2"),),0))

Index inside Match is not required but mimics the CSE behaviour. Without the index inside, you'll need to enter it as Array.

answered Jan 30, 2020 at 10:05

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INDEX MATCH array formula error for table with single row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related