index and match return custom message if #NA

[rjmdc]

hi
i got as far as to realize that index and match shouldnt have empty rows. i used power query to trim and clean so values are the same
index and match works but leaves me a messy sheet full of #NA
=INDEX('Vendors with Addresses'!A:A,MATCH([@[Tabs ID]],'Vendors with Addresses'!M:M,0))
how do i add custom message that if result is #NA instead "choose another method"

 

[rjmdc]

i got this far
=IF(IFERROR(INDEX('Vendors with Addresses'!A:A,MATCH([@[Tabs ID]],'Vendors with Addresses'!M:M,0)),"")="", "choose override",IFERROR(INDEX('Vendors with Addresses'!A:A,MATCH([@[Tabs ID]],'Vendors with Addresses'!M:M,0)),""))

however the entire column gets filled with the "override"
what am i missing to the formula that only if there is a field on [@[Tabs ID]] do formula i cant have an empty column filled

 

[Fluff]

How about
= IFERROR(INDEX('Vendors with Addresses'!A:A,MATCH([@[Tabs ID]],'Vendors with Addresses'!M:M,0)),"Choose override")

 

[rjmdc]

hi
it still fills the entire column with "override"
how do i get the unfilled cells to be ""

 

[Fluff]

Does the formula you posted in your op return anything other than #N/A?

 

[rjmdc]

no it also only gives me the "override"

 

[Fluff]

I'm talking about the original formula you posted in the op, not the formula you posted in post#2

 

[rjmdc]

the first formula gave me NA and the 1 or 2 matches

 

[Fluff]

In that case the formula I supplied should do the same, but you would get Choose override instead of #N/A

 

[rjmdc]

that is what happens, same as the formula i used
problem is the entire table even the unfilled rows gets filled with the overrride, is there a way for the ell to be blank unless it is actually used in the formula meaning cell A is filled in?