My formula in G10 is not working properly.
Here is the formula IF(D10="","",VLOOKUP(D10,BB$10:BC$167,2,0))
The problem: D is invoice# from 10 to 494
BB ranges from 10 to 167 and contains some invoice numbers that were verified
BC ranges from 10 to 167 and contains number of problems with the invoice
from 0 to whatever number.
If D=BB then I need G to display the number of problems from BC
I need D to remain blank if it does not match with BB.
So far, my formula is not working. I get many #N/A
Please help to correct this.
Thank you

column D and column BB must be the same format... i.e. both Numeric or
both Text.

If they are supposed to be numeric.. try selecting column D and go to
Data|Text to Columns and click Finish...

Repeat for column BB.

To get rid of N/A error..

=IF(D10="","",IF(ISNA(VLOOKUP(D10,BB$10:BC$167,2,0 )),"",VLOOKUP(D10,BB$10:BC$167,2,0))


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111433

Are we really working the numbers or text?
Suppose BB1 holds the value 123
In some empty cell type =BB1=123
Do you get TRUE? Then VLOOKUP should work.
Happy to look at the workbook if you send it to me (not to the newsgroup) --
just remove TRUENORTH.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Vic" wrote in message
...
My formula in G10 is not working properly.
Here is the formula IF(D10="","",VLOOKUP(D10,BB$10:BC$167,2,0))
The problem: D is invoice# from 10 to 494
BB ranges from 10 to 167 and contains some invoice numbers that were
verified
BC ranges from 10 to 167 and contains number of problems with the invoice
from 0 to whatever number.
If D=BB then I need G to display the number of problems from BC
I need D to remain blank if it does not match with BB.
So far, my formula is not working. I get many #N/A
Please help to correct this.
Thank you

Hi,
try
=sumproduct(--(D10=BB$10:BB$167),BC$10:BC$167)

"Vic" wrote:

My formula in G10 is not working properly.
Here is the formula IF(D10="","",VLOOKUP(D10,BB$10:BC$167,2,0))
The problem: D is invoice# from 10 to 494
BB ranges from 10 to 167 and contains some invoice numbers that were verified
BC ranges from 10 to 167 and contains number of problems with the invoice
from 0 to whatever number.
If D=BB then I need G to display the number of problems from BC
I need D to remain blank if it does not match with BB.
So far, my formula is not working. I get many #N/A
Please help to correct this.
Thank you

Try

=INDEX(BC$10:BC$167,MATCH(D10,BB$10:BB$167,0))

OR (to handle error)
=IF(ISNA(MATCH(D10,BB$10:BB$167,0)),"",INDEX(BC$10 :BC$167,MATCH(D10,BB$10:BB$167,0)))


--
If this post helps click Yes
---------------
Jacob Skaria


"Vic" wrote:

My formula in G10 is not working properly.
Here is the formula IF(D10="","",VLOOKUP(D10,BB$10:BC$167,2,0))
The problem: D is invoice# from 10 to 494
BB ranges from 10 to 167 and contains some invoice numbers that were verified
BC ranges from 10 to 167 and contains number of problems with the invoice
from 0 to whatever number.
If D=BB then I need G to display the number of problems from BC
I need D to remain blank if it does not match with BB.
So far, my formula is not working. I get many #N/A
Please help to correct this.
Thank you