CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Luiz
Luiz

Reputation: 101

Returning #N/A when using IFS with MATCH

I'm using IFS and MATCH in this function below to select a specific value from a condition, but it is returning error.

IFS(MATCH(C4, A2, 0), A3, MATCH(C4, A3, 0), A2, MATCH(C4, B2, 0), B3, MATCH(C4, B3, 0), B2)

I've noticed that when reordering the formula above to the one below, if the MATCHcondition that returns TRUE comes first, IFS will read it first and output the expected value E; so it's probably some issue with the other "MATCHes" not finding the C4 value in the condition and returning #N/A, as you can see in this spreadsheet example.

IFS(MATCH(C4, B2, 0), B3, MATCH(C4, A2, 0), A3, MATCH(C4, A3, 0), A2, MATCH(C4, B3, 0), B2)

Upvotes: 0

Views: 473

Answers (2)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

If a logical_test argument is evaluated and resolves to a value other than TRUE or FALSE, this function returns a #VALUE! error so I added isnumber function so its result only true or false, Now maybe it will work, try this formula

E3=IFS(ISNUMBER(MATCH(C4, A2, 0)), A3, ISNUMBER(MATCH(C4, A3, 0)), A2, ISNUMBER(MATCH(C4, B2, 0)), B3, ISNUMBER(MATCH(C4, B3, 0)), B2)

or

E3=IFS(C4=A2,A3,C4=A3,A2,C4=B2,B3,C4=B3,B2)

enter image description here

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36880

As per your example, please try below formula.

=IFERROR(MATCH(C4,A2:A3,0),MATCH(C4,B2:B3,0))

Upvotes: 0

Related Questions