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

 

[NBVC]

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

 

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