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

 

[Fluff]

Account details updated.

Thanks for that

Let's get the vlookup working 1st. Did you try my suggestion?

 

[JamesCanale]

SUMPRODUCT for sumif:

MrExcelPlayground12.xlsx
	A	B	C	D	E
1	1	100	2	200	600
2	2	200
3	3	300
4	2	400
5	5	500
6	6	600
Sheet9

Cell Formulas
Range	Formula
D1	D1	=INDEX(B1:B6,MATCH(C1,VALUE(A1:A6),0))
E1	E1	=SUMPRODUCT(VALUE(B1:B6),--(VALUE(A1:A6)=C1))

 

[manekankit]

Thanks for that

Let's get the vlookup working 1st. Did you try my suggestion?

Sorry for delay, I am traveling, will reach in 15 minutes and confirm. Thanks.

 

[Fluff]

Ok

 

[manekankit]

Index Match here. XLOOKUP aught to work if you have 365.

MrExcelPlayground12.xlsx
	A	B	C	D
1	1	a	2	b
2	2	b
3	3	c
4	4	d
5	5	e
6	6	f
Sheet9

Cell Formulas
Range	Formula
D1	D1	=INDEX(B1:B6,MATCH(C1,VALUE(A1:A6),0))

match and xlookup resulted in error. however text and sumproduct value resulted

Thanks for that

Let's get the vlookup working 1st. Did you try my suggestion?

vlookup is not working. also tried xlookup in office 365. but not working.

189150	100​	299070​	#N/A​	=MATCH(C1,A1:A5,0)
189150	200​		#VALUE!​	=XLOOKUP(C1,A:A,1,0)
299070	300​		#VALUE!​	=VLOOKUP(C1*" ",A1:B5,2,0)

 

[Fluff]

In the vlookup you need to change the * for an & and also remove the space between the quotes.

 

[manekankit]

In the vlookup you need to change the * for an & and also remove the space between the quotes.

Perfect! vlookup, text and sumproduct formulas are working.
Thanks for your time and support. pasting below for ready reference.

189150	100​	299070​	#N/A​	=MATCH(C1,A1:A5,0)
189150	200​		#VALUE!​	=XLOOKUP(C1,A:A,1,0)
299070	300​		300​	=VLOOKUP(C1&"",A1:B5,2,0)
188998	400​		300​	=VLOOKUP(TEXT(C1,0),A1:B5,2,0)
299070	500​		800​	=SUMIF(A1:A5,TEXT(C1,0),B1:B5)
			800​	=SUMPRODUCT(VALUE(B1:B5),--(VALUE(A1:A5)=C1))
			#N/A​	=VLOOKUP(VALUE(C1),A1:B5,2,0)

 

[manekankit]

Book1
	A	B	C	D
1	123	$ 100.00	150	$ 230.00
2	124	$ 120.00	131	$ 220.00
3	140	$ 140.00	130	$ 210.00
4	126	$ 160.00	160	$ 200.00
5	127	$ 180.00	128	$ 190.00
6	128	$ 190.00	127	$ 180.00
7	160	$ 200.00	126	$ 160.00
8	130	$ 210.00	140	$ 140.00
9	131	$ 220.00	124	$ 120.00
10	150	$ 230.00	123	$ 100.00
Sheet1

Cell Formulas
Range	Formula
D1:D10	D1	=VLOOKUP(TEXT(C1,0),$A$1:$B$10,2,FALSE)

thanks for the help and reply. the formula given by you is also perfectly working apart from the formula given by Fluff.

 

[manekankit]

SUMPRODUCT for sumif:

MrExcelPlayground12.xlsx
	A	B	C	D	E
1	1	100	2	200	600
2	2	200
3	3	300
4	2	400
5	5	500
6	6	600
Sheet9

Cell Formulas
Range	Formula
D1	D1	=INDEX(B1:B6,MATCH(C1,VALUE(A1:A6),0))
E1	E1	=SUMPRODUCT(VALUE(B1:B6),--(VALUE(A1:A6)=C1))

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.

 

[Fluff]

Glad we could help & thanks for the feedback.