Excel Spreadsheet help required please...

[Bazz]

I thought one of you techie guys or gals may be kind enough to help with a little problem that is doing my head in...

I have a need to enter some text and get Excel do do a vlookup to a Table Array in a Named Range on another sheet and return the value from the column 3 to the adjoining cell on the first sheet.

All this I can do and it works fine, the problem I have is that if the data I have entered is not found Excel returns the value of what it thinks is the closest match. This is no good as I may not pick up the error.

What I would like to achieve is for a "0" value to be returned where the text is not a match (it does not have to be case sensitive and will sometimes contain spaces).

Here is the formula I am using currently:

=IF(E4=0,0,VLOOKUP(E4,DISTANCE,3))

Where E4 contains the data to be looked up
Distance is the Named Range containing the Table Array
I am using 0 rather than "" because I then need to sum the returned values.

It is probably blindingly obvious but I have been looking at it for so long now I can't see the wood for the trees! [?]

Any ideas?

Thanks.
Barry.

[Pete]

Make that =IF(E4=0,0,VLOOKUP(E4,DISTANCE,3,FALSE)) to force an exact match.

I would use the iserror function. I am not by an excel pc at the moment so by memory
=if(iserror(IF(E4=0,0,VLOOKUP(E4,DISTANCE,3))),0,(IF(E4=0,0,VLOOKUP(E4,DISTANCE,3)))
or
=if(iserror(IF(E4=0,0,VLOOKUP(E4,DISTANCE,3)),0,(IF(E4=0,0,VLOOKUP(E4,DISTANCE,3)))
can't remember which it is.

In essence it is saying that is there is an error with the formula 'IF(E4=0,0,VLOOKUP(E4,DISTANCE,3))' then it will return a value of 0 otherwise it will return the outcome of the formula.

Hope that helps
Chris

A combo of Pete and Chris's...

=IF(ISERROR(IF(E4=0,0,VLOOKUP(E4,DISTANCE,3,FALSE))),0,IF(E4=0,0,VLOOKUP(E4,DISTANCE,3,FALSE)))

As that's not exactly readable, I'd be tempted to spilt it out into two columns, first returning the result of the Vlookup (which may be #NA if no exact match is found) and the second testing for the #NA condition and putting a 0 if found. Then hide the first column.

Thus (assume column F is inserted...)

=IF(E4=0,0,VLOOKUP(E4,DISTANCE,3,FALSE))

and the next column

=IF(ISERROR(F4),0,F4)

Simplifying further (not much on today...), if zero is not an entry in your "DISTANCE" array then the column F could be a simple lookup without the IF() condition i.e.

=VLOOKUP(E4,DISTANCE,3,FALSE)

Zero in E2 would then produce an error condition which the error trapping column would then convert to zero.

Same result but a bit more readable. Debugging many Excel sheets over the years has made me a big fan of simplicity in any given cell formula.

Paul

[Bazz]

Thanks guys, I appreciate it, I have gone with Paul's formula as it returns a "0" if there is a mismatch, thanks to Pete and Chris for their speedy replies.

Isn't this site a wonderful place? [y]