ISNA with multiple vlookup - want to return Yes/No

Hello. I have been given a project that needs to cross reference multiple spreadsheets.

I have created a (rather messy) long multiple v-lookup that matches dealer numbers, what I am after is the status of customers direct debit payments. I have this working and the ISNA part of the function is returning true/false - but these are the wrong way round as it is currently testing to see if the result is NA.

I have tried putting "Yes", "No" values at various places in between brackets but always get an error.

Formula:

Please Login or Register  to view this content.

Formula is shown above, the names eg "cindy" etc are data ranges as this made the formula easier to read/write.

My question is, what can I add to that formula to get a Yes/No return. Have experimented with using match as well, but can only get this working for one sheet. I am sure it's just me mistyping the formula here.

Thank you for taking the time to read and any help is appreciated

It looks like you simply want to know if the lookup value A3 can be found in column 1 of any of the named ranges.

Try something like this...

=IF(COUNTIF(INDEX(cindy,,1),A3)+COUNTIF(INDEX(core,,1),A3)+COUNTIF(INDEX(sek,,1),A3)+COUNTIF(INDEX(chf,,1),A3)+COUNTIF(INDEX(nok,,1),A3)+COUNTIF(INDEX(silke,,1),A3)+COUNTIF(INDEX(gbp,,1),A3),"Yes","No")

Originally Posted by pilot_jp

trying this throws an error code that the value used in the formula is of the wrong data type - these are all numbers being looked up.

Any ideas what the error might mean?

Hard to say without seeing it in context.

Here's an abbreviated sample that demonstrates this.

You're welcome. Thanks for the feedback!