VLOOKUP

[Clay_TX]

My first time to use this site, but looks like Pete_UK could probably help me
out.

I have an array which contains two columns, a text string in the first and
numeric values in the second. My problem is since I want to use 'exact'
match, VLOOKUP returns #na when not found. I need the return value to always
be numeric 0 if not found, rather than the #na, as the cells populated by the
formula are totaled, and =sum does not work with any #na value in the column.

Is there a way of making VLOOKUP do what I need, or some other
function/formula which would work instead.

Thanks,

 

[Dave Peterson]

If you're using xl2007, you could look at =iferror().

If you're using xl2003 or below, you can look twice:

=if(isna(vlookup(...)),0,vlookup(...))

 

[Clay_TX]

Thanks Dave, that worked just great. Forgive me for thinking only Pete_UK
could help. Good to know there are so many sources of information for a
fairly novice at this.

Does VLOOKUP general help screens in Excel give this kind of ISNA
combination example, or is this just something one has to experiment with on
their own?

Thanks again,

 

[Dave Peterson]

Excel's help talks about returning errors, but I didn't see anything that
explained how to use =isna() inside the help for =vlookup().

But there are lots of resources available.

Google can help you search for similar questions/responses:
http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())

Thanks Dave, that worked just great. Forgive me for thinking only Pete_UK
could help. Good to know there are so many sources of information for a
fairly novice at this.

Does VLOOKUP general help screens in Excel give this kind of ISNA
combination example, or is this just something one has to experiment with on
their own?

Thanks again,

 

[Pete_UK]

Thanks for the vote of confidence, Clay, but Dave (along with many
others) has been doing this a lot longer than I have.

Pete