VLOOKUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clay_TX - 29 Mar 2008 21:54 GMT

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,

Signature

Clay

Reply to this Message

Dave Peterson - 29 Mar 2008 22:01 GMT

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(...))

> My first time to use this site, but looks like Pete_UK could probably help me
> out.
[quoted text clipped - 11 lines]
> --
> Clay

Signature

Dave Peterson

Reply to this Message

Clay_TX - 29 Mar 2008 22:59 GMT

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,

Signature

Clay

> If you're using xl2007, you could look at =iferror().
>
[quoted text clipped - 17 lines]
> > --
> > Clay

Reply to this Message

Dave Peterson - 29 Mar 2008 23:48 GMT

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
[quoted text clipped - 33 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Pete_UK - 30 Mar 2008 02:09 GMT

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

Pete

> 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
[quoted text clipped - 35 lines]
>
> - Show quoted text -

Reply to this Message