Issue with vlookup

[manekankit]

Having below issue while applying vlookup

Column A1 to 100 contains customer codes, all numerical

Column b1 to 100 contains customer balances

A and B values are loaded directly from accounting software through Excel add-in. Column A values are all digits but loaded in text format.

In cell c1, i type customer code manually.

I want to apply vlookup in cell D1, that will lookup c1 value in column A and give balance from column B.

But as values in column A are stored in text format, vlookup formula is resulting in an error.

If i conver all values in column A to numbers, vlookup works fine.

Is there any way out through vlookup or other formula to get values in cell D1 without converting column A values to numbers

 

[JamesCanale]

hi thanks for your time and reply. Index/Match formulas not working.

Sumproduct formula is working pefectly, however i am unable to comprehend the formula as i have never used such formula. Can you share some link to understand the structure of such type of formulas.

I can't say why the index/match isn't working for you, but you seem to have some good other options...

Sumproduct is very handy with older versions of excel.

In this case, it just multiplies together the first expression VALUE(B1:B6) and the second expression --(VALUE(A1:A6)=C1 - and then adds them all up.

The first expression is simple,
100
200
300
400
500
600

The second expression can be broken up:
VALUE(A1:A6)=C1 is:
FALSE
TRUE
FALSE
TRUE
FALSE
FALSE

In excel, TRUE is 1 and FALSE is zero. But to get them to that, you have to do some identity math to them, like multiply by 1, add zero, or the common one is to double-negative it.
So, --(VALUE(A1:A6)=C1) becomes
0
1
0
1
0
0

So that leaves you with 100*0+200*1+300*0+400*1+500*0+600*0 = 600.

 

[manekankit]

I can't say why the index/match isn't working for you, but you seem to have some good other options...

Sumproduct is very handy with older versions of excel.

In this case, it just multiplies together the first expression VALUE(B1:B6) and the second expression --(VALUE(A1:A6)=C1 - and then adds them all up.

The first expression is simple,
100
200
300
400
500
600

The second expression can be broken up:
VALUE(A1:A6)=C1 is:
FALSE
TRUE
FALSE
TRUE
FALSE
FALSE

In excel, TRUE is 1 and FALSE is zero. But to get them to that, you have to do some identity math to them, like multiply by 1, add zero, or the common one is to double-negative it.
So, --(VALUE(A1:A6)=C1) becomes
0
1
0
1
0
0

So that leaves you with 100*0+200*1+300*0+400*1+500*0+600*0 = 600.

Beautifully explained.

I had seen such formulas with double -- earlier, but could not understand.

But the way you have explained is amazingly simple.

Thanks again.