Using Vlookup for Alphanumeric and numeric values. It is not recognising the numeric

davids4500 · Saturday at 9:56 PM

I am using vlookup in excel 365. Column A is where the product number is scanned and can be alpha numeric or numeric.
Excel finds the matches for the alpha numeric but not the numeric.
Column A where the barcodes are scanned is formatted General.
The columns on the data sheet for the alpha numeric are formatted general and the numeric is number.
How do I get the scanned numeric barcode to successfully interpret the value in the data column in the data sheet.

SamplesScansheetC&S.xlsx

A

B

C

1

Scan Code Here

Product Code

Product Name

2

CCH000084

Air Freshener I Brake For Turtles

3

CCH000085

Air Freshener Bouquet

4

AFR205

Air Freshener Pink Heart - Strawberry

5

6.14391E+11

6

6.14391E+11

7

6.14391E+11

8

6.14391E+11

9

ScanSheet

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(VLOOKUP(A2,data!A:C,COLUMN(A2)*1,FALSE), "")
C2:C8	C2	=IFERROR(VLOOKUP($A2,data!A:D,COLUMN(D2)*1,FALSE), "")

Named Ranges
Name	Refers To	Cells
data!samplestoday	=data!$A$1:$R$809	B2:C8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A67:A68,A5:A8	Cell Value	contains "Sample"	text	NO

SamplesScansheetC&S.xlsx

A

B

C

D

1

Item Code

SUPPLIER CODE

ITEMCODE2

NAME

2

AFR021

614390300994

Air Freshener Free To Fly

3

AFR037

614390308983

Air Freshener Friends Fill

4

AFR065

614390341799

Air Freshener Truck Don't Look Back (Ocean)

5

AFR157

614390545050

Air Freshener Beautiful Girl - Strawberry

6

AFR158

614390545043

Air Freshener Stay Classy

7

AFR159

614390545012

Air Freshener A Wise Girl Once Said

8

AFR172

614390557077

Air Freshener Make Difference Today

9

AFR176

614390557114

Air Freshener I Heart My Dog

10

AFR180

614390561609

Air Freshener AICIS NOT APPROVED Let's Just Go

11

AFR191

614390585605

Air Freshener Stay Close

12

AFR192

614390585612

Air Freshener Sometimes

13

AFR205

614390614985

Air Freshener Pink Heart - Strawberry

14

AFR208

614390615012

Air Freshener Kindness Matters

15

AFR209

614390615029

Air Freshener Be Happy Van Orange

16

AFR213

614390642520

Air Freshener Always Remember

17

AFR219

614390642575

Air Freshener Mushroom

18

AFR220

614390642582

Air Freshener Butterfly - Ocean

data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	contains "Sample"	text	NO

coulombevin · Saturday at 10:22 PM

Hi David,

The problem is not the vlookup, but the way you use it. COLUMN(A2) will return 1, so your function return the cell value where it finds it. If you look for CCH000084, it will return the same cell which contain CCH000084. For the number, I would put an iferror in an iferror like so:

Excel Formula:

=IFERROR(VLOOKUP(A2,data!$A:$D,4,FALSE), IFERROR(VLOOKUP(A2,data!$C:$D,2,FALSE), ""))

Bests regards,

Vincent

davids4500 · Saturday at 10:57 PM

Thank you so much. Very good
=IFERROR(VLOOKUP(A2,data!$A:$D,1,FALSE), IFERROR(VLOOKUP(A2,data!$C:$D,2,FALSE), ""))
I have changed the formula(1) so that the first part returns column A from the data worksheet. How do i accomplish that Returns the data in Column A), with the second part of the formula?
I am so very thankful
David

coulombevin · Saturday at 11:13 PM

davids4500 said:
Thank you so much. Very good
=IFERROR(VLOOKUP(A2,data!$A:$D,1,FALSE), IFERROR(VLOOKUP(A2,data!$C:$D,2,FALSE), ""))
I have changed the formula(1) so that the first part returns column A from the data worksheet. How do i accomplish that Returns the data in Column A), with the second part of the formula?
I am so very thankful
David

Hi david,

Now I think I understand, in any way, you want to get the value of the first column. Here's for Product Code

Excel Formula:

=IFERROR(VLOOKUP($A2,data!$A:$A,1,FALSE), IFERROR(XLOOKUP($A2,data!$C:$C,data!$A:$A,"",0), ""))

For Product Name

Excel Formula:

=IFERROR(VLOOKUP($B2,data!$A:$D,4,FALSE),"")

Bests regards,

Vincent

davids4500 · Saturday at 11:16 PM

Dear Vincent
Thank you for taking the time.
Much appreciated and very much helpful... very good result
Kindest regards David

Peter_SSs · 2025-01-26T00:31:54-0500

A couple of short alternatives you could also try if you want

Excel Formula:

=LET(a,data!A:A,XLOOKUP(A2,a,a,XLOOKUP(A2,data!C:C,a,"")))

Excel Formula:

=XLOOKUP(B2,data!A:A,data!D:D,"")

davids4500 · 2025-01-26T04:26:59-0500

davids4500 said:
Dear Vincent
Thank you for taking the time.
Much appreciated and very much helpful... very good result
Kindest regards David

Hi Vincent. I have found one anomaly that if there is no code column A (Scan sheet) then it displays the first product where there is no code in column C on the data sheet

SamplesScansheetC&S.xlsx

A

B

C

D

577

MUG000068

614390683912

Camp Mug Mountains

578

MUG000069

614390683929

Camp Mug Mushroom

579

MUG000072

614390684421

Mushroom Mug with Lid Grow Own Way

580

MUG000073

614390684711

Favourite Mug Do More Soul Happy

581

MUG000074

614390685367

Rainbow Mug You Make The World Better

582

MUG000075

614390686050

Folk Art Mug Cream Turtle

583

MUG000076

614390686067

Folk Art Mug Cream Camper

584

MUG000077

Artisan Rainbow Courage Mug

585

MUG000078

614390692150

Artisan Rainbow Gratitude Mug

586

MUG000079

614390696417

Latte Mug The Place Mountain Range

587

MUG000080

614390696424

Bungalow Mug Every Home Has A Dog

588

MUG000082

614390696448

Artisan Mug Good Friends Mushrooms

data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	contains "Sample"	text	NO

SamplesScansheetC&S.xlsx

A

B

C

315

PLNT016

Critter Succulent Hedgehog

316

6.14391E+11

ART158

Wall Decal Do What Makes You Happy

317

plnt016

PLNT016

Critter Succulent Hedgehog

318

MUG000077

Artisan Rainbow Courage Mug

319

MUG000077

Artisan Rainbow Courage Mug

320

MUG000077

Artisan Rainbow Courage Mug

321

MUG000077

Artisan Rainbow Courage Mug

322

MUG000077

Artisan Rainbow Courage Mug

323

MUG000077

Artisan Rainbow Courage Mug

ScanSheet

Cell Formulas
Range	Formula
B315:B317,B319:B323	B315	=IFERROR(VLOOKUP($A315,data!$A:$A,1,FALSE), IFERROR(XLOOKUP($A315,data!$C:$C,data!$A:$A,"",0), ""))
C315:C323	C315	=IFERROR(VLOOKUP($B315,data!$A:$D,4,FALSE),"")
B318	B318	=IFERROR(VLOOKUP($A318,data!$A:$A,1," "), IFERROR(XLOOKUP($A318,data!$C:$C,data!$A:$A,"",0), ""))

Named Ranges
Name	Refers To	Cells
data!samplestoday	=data!$A$1:$T$809	B315:C323

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A316	Cell Value	contains "Sample"	text	NO

Peter_SSs · 2025-01-26T04:57:26-0500

Then what about

B2:

Excel Formula:

=IF(A2="","",LET(a,data!A:A,XLOOKUP(A2,a,a,XLOOKUP(A2,data!C:C,a,""))))

C2:

Excel Formula:

=IF(B2="","",XLOOKUP(R2,data!A:A,data!D:D,""))

coulombevin · 2025-01-26T16:20:21-0500

davids4500 said:
Hi Vincent. I have found one anomaly that if there is no code column A (Scan sheet) then it displays the first product where there is no code in column C on the data sheet

SamplesScansheetC&S.xlsx
A B C D
577 MUG000068 MUG000068 614390683912 Camp Mug Mountains
578 MUG000069 MUG000069 614390683929 Camp Mug Mushroom
579 MUG000072 MUG000072 614390684421 Mushroom Mug with Lid Grow Own Way
580 MUG000073 MUG000073 614390684711 Favourite Mug Do More Soul Happy
581 MUG000074 MUG000074 614390685367 Rainbow Mug You Make The World Better
582 MUG000075 MUG000075 614390686050 Folk Art Mug Cream Turtle
583 MUG000076 MUG000076 614390686067 Folk Art Mug Cream Camper
584 MUG000077 MUG000077 Artisan Rainbow Courage Mug
585 MUG000078 MUG000078 614390692150 Artisan Rainbow Gratitude Mug
586 MUG000079 MUG000079 614390696417 Latte Mug The Place Mountain Range
587 MUG000080 MUG000080 614390696424 Bungalow Mug Every Home Has A Dog
588 MUG000082 MUG000082 614390696448 Artisan Mug Good Friends Mushrooms
data
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
C:C Cell Value contains "Sample" text NO

SamplesScansheetC&S.xlsx
A B C
315 PLNT016 PLNT016 Critter Succulent Hedgehog
316 6.14391E+11 ART158 Wall Decal Do What Makes You Happy
317 plnt016 PLNT016 Critter Succulent Hedgehog
318 MUG000077 Artisan Rainbow Courage Mug
319 MUG000077 Artisan Rainbow Courage Mug
320 MUG000077 Artisan Rainbow Courage Mug
321 MUG000077 Artisan Rainbow Courage Mug
322 MUG000077 Artisan Rainbow Courage Mug
323 MUG000077 Artisan Rainbow Courage Mug
ScanSheet
Cell Formulas
Range Formula
B315:B317,B319:B323 B315 =IFERROR(VLOOKUP($A315,data!$A:$A,1,FALSE), IFERROR(XLOOKUP($A315,data!$C:$C,data!$A:$A,"",0), ""))
C315:C323 C315 =IFERROR(VLOOKUP($B315,data!$A:$D,4,FALSE),"")
B318 B318 =IFERROR(VLOOKUP($A318,data!$A:$A,1," "), IFERROR(XLOOKUP($A318,data!$C:$C,data!$A:$A,"",0), ""))
Named Ranges
Name Refers To Cells
data!samplestoday =data!$A$1:$T$809 B315:C323
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
A316 Cell Value contains "Sample" text NO

Hi David,

The answer from Peter would suit your case. Although, if you want to keep the formulas I've made, here's how to prevent that error:

For Product Code

Excel Formula:

=IF($A2 = ""; ""; IFERROR(VLOOKUP($A2,data!$A:$A,1,FALSE), IFERROR(XLOOKUP($A2,data!$C:$C,data!$A:$A,"",0), "")))

For Product Name

Excel Formula:

=IF($A2="";"";IFERROR(VLOOKUP($B2,data!$A:$D,4,FALSE),""))

Bests regards,

Vincent

Using Vlookup for Alphanumeric and numeric values. It is not recognising the numeric

davids4500

Board Regular

Excel Facts

coulombevin

Board Regular

davids4500

Board Regular

coulombevin

Board Regular

davids4500

Board Regular

Peter_SSs

MrExcel MVP, Moderator

davids4500

Board Regular

Peter_SSs

MrExcel MVP, Moderator

coulombevin

Board Regular

Similar threads

Forum statistics

Share this page

Using Vlookup for Alphanumeric and numeric values. It is not recognising the numeric

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock