return null values from vlookup

[stewbrown]

I would like to have vlookup return a null or missing (or empty cell) when it does not find a match, instead of the "#N/A". Is there a way to do this?

 

[Aladin Akyurek]

Actually, it was neither, what I posted is in the formula.

=INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,)).

I had set it up using the Lookup Wizard. As far as working correctly, it gives the correct values (whenever there are values in the table). Will the missing zeroes help?

As for the morefunc - I have it, but not sure if it requires payment for it to work. How would I go about using it?

No, morefunc doesn't require payment. By the way, I should have said "The formula should be either ... or..." instead of "I expect your original formula to be either...or...".

Assuming that you installed the morefunc add-in, you can use:

=IF(ISNA(SETV(INDEX(Work!$B$1:$F$823,MATCH($D8,Work!$D$1:$D$823,0),MATCH("SOV",Work!$B$1:$F$1,0)))),"",GETV())

Try to experiment with the first MATCH with 0 and without 0 to see how that functions. The SETV/GETV allows you to avoid computing the same thing twice in order to get a result or a blank.

Aladin
This message was edited by Aladin Akyurek on 2002-10-08 17:56

 

[shades]

Thank you! This will be my project tomorrow morning.

I truly appreciate the time and advice.

 

[shades]

Just to let everyone know, I followed the advice here - and everything works perfect!!

Thank you!!!