Matching Info In A Cell To Data In A Table On Another Sheet

[Cavmoose]

Good day,

I am dealing with an issue of getting data to correspond to a table on a different sheet. I'll list out everything first:

Sheet 1:
Column A contains a barcode
Column B contains the second character of Column A (used mid to get that)
Column C is to contain information involving Column B based on information from a table on another sheet

Sheet 2:
Column A contains a list of numbers and letters (0-9 and then switches to A-Z)
Column B contains text that corresponds to each number or letter

What I am trying to do is when I scan a barcode, I want the text from Sheet 2 Column B to appear in Sheet 1 Column C based on the number or letter in Sheet 1 Column B.

I used the following to get it to work but it only seems to be working for the letters and not the numbers. Ex. If Sheet 1 B2=5, it will not display Sheet 2 Column B Text corresponding to 5. However it does work if B2=J. Formula =IFERROR(VLOOKUP(B2,Sheet2!$A$1:$B$32,2,FALSE),"All Off")

Thanks in advance!

 

[etaf]

when you use MID it turns the number into text
so a1jj
will be a text 1 and not a number
then you are looking up for a text 1

maybe a better way
but try this for your mid function
=IFERROR((MID(A2,2,1))*1,MID(A2,2,1))
then see if it looks up OK

or your lookup table - all the numbers need to be text

Book1
	A	B	C	D	E	F	G
1
2	abc	b		This is b		b	This is b
3	a12	1		This is 1		1	This is 1
4	fgh	g		This is g		g	This is g
5	111	1		This is 1		2	This is 2
6	122	2		This is 2
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	="This is "&F2
B2:B6	B2	=IFERROR((MID(A2,2,1))*1,MID(A2,2,1))
D2:D6	D2	=IFERROR(VLOOKUP(B2,$F$2:$G$5,2,FALSE),"All Off")

 

[Cavmoose]

when you use MID it turns the number into text
so a1jj
will be a text 1 and not a number
then you are looking up for a text 1

maybe a better way
but try this for your mid function
=IFERROR((MID(A2,2,1))*1,MID(A2,2,1))
then see if it looks up OK

or your lookup table - all the numbers need to be text

Book1
	A	B	C	D	E	F	G
1
2	abc	b		This is b		b	This is b
3	a12	1		This is 1		1	This is 1
4	fgh	g		This is g		g	This is g
5	111	1		This is 1		2	This is 2
6	122	2		This is 2
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	="This is "&F2
B2:B6	B2	=IFERROR((MID(A2,2,1))*1,MID(A2,2,1))
D2:D6	D2	=IFERROR(VLOOKUP(B2,$F$2:$G$5,2,FALSE),"All Off")

That worked! Thank you so much!

 

[etaf]

you are welcome
there maybe a better way to do - i seem to remember something, but cant work it out ....