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,
--
Clay

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 wrote:

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,
--
Clay

--

Dave Peterson

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,
--
Clay


"Dave Peterson" wrote:

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 wrote:

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,
--
Clay

--

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_gr...Excel*&num=100

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



Clay_TX wrote:

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,
--
Clay

"Dave Peterson" wrote:

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 wrote:

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,
--
Clay

--

Dave Peterson


--

Dave Peterson

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

Pete

On Mar 29, 10:59*pm, Clay_TX wrote:
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,
--
Clay



"Dave Peterson" wrote:
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 wrote:

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,
--
Clay

--

Dave Peterson- Hide quoted text -

- Show quoted text -