Need help with VLOOKUP

[Vic]

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

 

[Bernard Liengme]

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

 

[Eduardo]

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

 

[Jacob Skaria]

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