Issue with vlookup

manekankit

Board Regular
Joined
Feb 1, 2019
Messages
72
Office Version
  1. 365
Platform
  1. Windows
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
 
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.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top