Excel 2010 using ISNA in and IIF to do something

[DougP]

We are getting #NA when a cell can't find the value. I stumbled across the ISNA function which now returns the word True. Is there a way to have an IIF ISNA = True then maybe run a function to do something else about finding the value? It’s a persons name and one system leaves out the Middle initial. The lookup tab “Base” has teh Middle intial so it does not find a match.
=ISNA(VLOOKUP(B80, Base!D:AZ,44,[highlight #FCE94F]FALSE[/highlight]))
I also tried changing the FALSE parameter to TRUE but that does not seem to work either.
this is my attempt to add the IIF
=IIF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE)=True,Personal.XLSB!FindName,VLOOKUP(B80, Base!D:AZ,44,FALSE))
BTW Personal.XLSB!FindName is a VBA function that finds a name.
Any ideas/suggestions welcome including VBA code


DougP

 

[DougP]

Ok I got thie to work. I forgot to add the paramtyers to my VBA function

=IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE))=TRUE,PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE)))

DougP

 

[madonnac]

if you're not worried about other errors that may be returned by a function, IFERROR provides a slightly shorter format.

=IFERROR(VLOOKUP(B80, Base!D:AZ,44,FALSE),PERSONAL.XLSB!FindName(B80,"BASE"))

 

[DougP]

Thanks madonnac, I did see that function, but the ISNA is what we are looking for. The #NA was taken care of manually by the user, but now this wippy-dippy one liner should work great



DougP

 

[xlbo]

just FYI, you do not need the =TRUE part

=IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE))=TRUE,PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE)))

can become

=IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE)),PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE)))

as ISNA returns true or false and the 1st part of the IF statement evaluates to true or false it is unnecessary

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

another thing to note is that if you have a large data set, you may be better off testing for NOT(ISNA)

Due to the way excel calculates, you are better off setting the true part of the IF formula to the most common occurence

If you expect more NAs than matched values then leave as is. If you expect that most values will be matched then:

=IF(NOT(ISNA(VLOOKUP(B80,Base!D:AZ,44,FALSE))),VLOOKUP(B80,Base!D:AZ,44,FALSE),PERSONAL.XLSB!FindName(B80,"BASE"))

should be quicker

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question