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]

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

 

[SanjayGMusafir]

Did you try converting C1 to Text format?

 

[manekankit]

Did you try converting C1 to Text format?

Thanks for your suggestion.

How to do that?

My file is structured such that i don't want to make any changes to columns a, b and even c.

Can values in c1 be converted through any formula in D1, if so, i can combine vlookup formula in d1 and then it should work.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try using

=Vlookup(C1&"",A1:B100,2,0)

 

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

Thanks for the reply.

I also want to use c1 as criteria for sumif in say E1 cell. So, how can i do that?

 

[Fluff]

You said you wanted to use Vlookup, so why are you now saying Sumif?

 

[manekankit]

You said you wanted to use Vlookup, so why are you now saying Sumif?

Hi, sorry for inconvenience.

I want to use both, both formulas resulting in error.

To avoid longer post, i gave example of vlookup.

 

[manekankit]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try using

=Vlookup(C1&"",A1:B100,2,0)

Account details updated.

 

[ExceLoki]

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)