Automatic population

[ctjacobs2010]

I am wanting to have many ISBNs in column 1. In column 2, I want to be able to type the last 4 numbers of an isbn from the column one list and then have the whole ISBN show up in column 3.

 

[BGY23]

Hi,
I don't know a lot about ISBN's but to do what you want the last 4 digits in every case are going to have to be unique to get a vlookup to work. If you want it to list multiple ISBN's with the same last 4 digits I'd be using a pivot table.

Col 2 ISBN's
Col 1 =Right(b1,4)
Col 3 If last 4 dgits are unique then a simple vlookup based upon Col1 and 2.

If not unque a pivot table based on Col 1 and 2.

 

[ctjacobs2010]

VLOOKUP I think???

I am wanting to have many 13 digit numbers in column 1. In another column , I want to be able to type the last four numbers of any of the number from the column one list and then have the whole number show up in column 3.


Ex:
1234567891234 7238 1234560587238
1234567892345
1234567893456
1234567893456
1234567898796
1234567868343
1234567808086
1234567898934
1234567058763
1234567883248
1234506789324
1234560587238
1234568689728
1234567578577
1234577777777
1234500000000
1234599999999
1234554545454
1234562528200

 

[Joe4]

Re: VLOOKUP I think???

Can you guarantee that the last four digits are ALWAYS unique?

Because if you have options like:
8888888881234
9999999991234

Then if you type in "1234", how will it know which value it should return?

Note: I have merged your two similar threads together.
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

 

[wideboydixon]

Re: VLOOKUP I think???

Easy enough with a helper column but tricky without; you may need VBA:

Book1
	A	B	C	D
1	1234567891234	1234	8086	1234567808086
2	1234567892345	2345
3	1234567893456	3456
4	1234567893456	3456
5	1234567898796	8796
6	1234567868343	8343
7	1234567808086	8086
8	1234567898934	8934
9	1234567058763	8763
10	1234567883248	3248
11	1234506789324	9324
12	1234560587238	7238
13	1234568689728	9728
14	1234567578577	8577
15	1234577777777	7777
16	1234500000000	0
17	1234599999999	9999
18	1234554545454	5454
19	1234562528200	8200
Sheet1

Cell Formulas
Range	Formula
D1	=INDEX($A$1:$A$19,MATCH($C$1,$B$1:$B$19,0))
B1	=RIGHT($A1,4)+0

WBD

 

[ctjacobs2010]

Re: VLOOKUP I think???

This is wonderful and exactly what I needed! Is there a conditional formatting that I could use that says If cell b1 equals any of the numbers in cells A1-A100 then turn red, otherwise, keep white?

 

[wideboydixon]

Re: VLOOKUP I think???

With my example above, you'd use the following formula for conditional formatting:

=ISNUMBER(MATCH($C$1,$B$1:$B$19,0))

WBD

 

[ctjacobs2010]

Re: VLOOKUP I think???

You are simply the best! Thanks a million