VLOOKUP challenge

  • Thread starter Thread starter brian.baker13
  • Start date Start date
B

brian.baker13

Hi

I am building a standard report template where the format does not
alter. I pull information from another table into this standard
report. The trouble is that the report I pull from changes so when I
have a formula =VLOOKUP(A674,B11:AW673,11,0) and there is no
information I get a #n/a error. I would like to have a 0 or blank
returned to enable totalling to work as some cells have figures in
them whilst others have n/a which prevents
=SUM(F696:F697) etc from working.


Cheers

Brian
 
Try something like this:

=IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
=IF(ISNA(vlookup formula),"",(vlookup formula)

Returns a blank.


Gord Dibben MS Excel MVP
 
Try something like this:

=IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)







- Show quoted text -
Click to expand...

Hi Ron

This works but I have an =sum(xxx) a couple of lines below referencing
this cell and others but I am getting returned a 0 even when figures
are in the other cells

Ta

Brian
 
Hi

I am building a standard report template where the format does not
alter. I pull information from another table into this standard
report. The trouble is that the report I pull from changes so when I
have a formula =VLOOKUP(A674,B11:AW673,11,0) and there is no
information I get a #n/a error. I would like to have a 0 or blank
returned to enable totalling to work as some cells have figures in
them whilst others have n/a which prevents
 =SUM(F696:F697) etc from working.

Cheers

Brian
Click to expand...

Hi Gary

This works but a cell that sums those above including a674 now returns
an error how do I update =SUM(B672:B674).

Ta

Brian
 
Hi Brian,

the "numbers" in your data table are probably text values which just
look like numbers - try this amendment to Ron's formula:

=IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0)+0,0)

This should turn the values into numbers.

Hope this helps.

Pete
 
Hi Brian,

the "numbers" in your data table are probably text values which just
look like numbers - try this amendment to Ron's formula:

=IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0)+0,0)

This should turn the values into numbers.

Hope this helps.

Pete






- Show quoted text -
Click to expand...

Cheers

Works a treat
 
Back
Top