Excel- Lookup 2

[wayneryeven]

Hi All
I have the followi9ng formula that essentially checks a cell to ensure it has a value that isnt empty or says "holiday" and then proceeds to do a lookup into a pivot table to retrieve a value.

I am getting N/A errors now when the lookup fails so i want to wrap the formula in a ISNA or ISERROR but i am having some trouble getting it right....can anyone point me in the right direction?

The forumla so far is:
=IF(ISBLANK(C$25),"",IF(C$25="Holiday","",VLOOKUP($A26,INDIRECT("Analysis"),MATCH(C$25,INDIRECT("Analysis_TopRow")))))

Just need somehow to stop the N/A errors coming up....!

Thanks to all who gives me their time and advise,
W

 

[SkipVought]

Hi,

Means that the Column A lookup value does not exist in column 1 of the "Analysis" table.

Do an IF test of the vlookup like this...
[tt]
=IF(ISBLANK(C$25),"",IF(C$25="Holiday","",IF(ISNA(VLOOKUP($A26,INDIRECT("Analysis"),MATCH(C$25,INDIRECT("Analysis_TopRow")))),"",VLOOKUP($A26,INDIRECT("Analysis"),MATCH(C$25,INDIRECT("Analysis_TopRow"))))))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wayneryeven]

Skip- the rumours are true...you are a legend!

Cheers bud.

 

[SkipVought]

Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I have always imagined that multiple IF statements should be avoided if possible. This uses a single one with and OR to test for the three alternative conditions thatshould result in a "". Often this makes (imho) the formula slightly easier to understand - though I am not sure in this case!

=if([red]or(ISBLANK(C$25),C$25="Holiday",isna(VLOOKUP($A26,INDIRECT("Analysis"),MATCH(C$25,INDIRECT("Analysis_TopRow"))))))[/red],"",VLOOKUP($A26,INDIRECT("Analysis"),MATCH(C$25,INDIRECT("Analysis_TopRow"))))))

Gavin

 

[SkipVought]

I actually rarely use VLOOKUP. I rather prefer using INDEX & MATCH, especilly since my data is often arranged in a way that prevents the use of VLOOKUP and because I use Column Range Names (Insert > Name > Create - Create names in TOP row).

In that event, to prevent #NA...
[tt]
=IF(ISNA(MATCH(LookupValue,LookupRange,0)),"",INDEX(ReturnRange,MATCH(LookupValue,LookupRange,0),1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Totally see your point Skip (though old habits die hard!). ==>>*

I am currently working on a procedure involving pulling multiple data from another file based on a single match. Replacing my multiple vlookups with your suggested approach but with the further aid of a helper column to hold the match should help me greatly reduce processing time.

When I have done that I will post separately for other suggestions... maybe I should be using Query...

Gavin

 

[SkipVought]

"...from another file ..." would lead me to use MS Query, most likely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}