ISNA Problem with Formula

[MrData]

I currently have a lookup formula =IF(MID(VLOOKUP($A$2,Summary,($C6),FALSE),3,3)="Air",VLOOKUP($A$2,Summary,($C6),FALSE),"") which shows #n/a on occasions. I know that if I use ISNA somewhere this should just show a blank cell if no match is found.

Can someone please advise where ISNA should be placed in the above formula, as all I do is get a blank cell where a result should be shown.

 

[Cbrine]

I currently have a lookup formula =IF(MID(VLOOKUP($A$2,Summary,($C6),FALSE),3,3)="Air",VLOOKUP($A$2,Summary,($C6),FALSE),"") which shows #n/a on occasions. I know that if I use ISNA somewhere this should just show a blank cell if no match is found.

Can someone please advise where ISNA should be placed in the above formula, as all I do is get a blank cell where a result should be shown.

=if(isna(IF(MID(VLOOKUP($A$2,Summary,($C6),FALSE),3,3)="Air",VLOOKUP($A$2,Summary,($C6),FALSE),"")),"",IF(MID(VLOOKUP($A$2,Summary,($C6),FALSE),3,3)="Air",VLOOKUP($A$2,Summary,($C6),FALSE),""))

Should do the job.

 

[MrData]

Thanks, that's worked. Would never have thought of adding an extra IF statement.

 

[Aladin Akyurek]

An efficient way with Morefunc...

=IF(ISNUMBER(--(MID(SETV(VLOOKUP($A$2,Summary,$C6,0)),3,3)="Air"),GETV(),"")