Lookup up Comma and non Comma separated values

[rambojr]

Hi I am trying to lookup numbers in multiple worksheets that have comma separated and non comma separated values. I have used the formula below but it only works in cells that have conman separated values.

=IFERROR(VLOOKUP(B$1,'Data Sheet'!$A$1:$B$3,2,FALSE),IFERROR(VLOOKUP(B$1,'Data Sheet2'!$A$1:$B$2,2,FALSE),""))

The result of the formula above is "RED" if I try to find code 8 it does not return a result, is there a way to to update the query to search for comma and non comma separated values.


Lookup Sheet

	A	B
1	Code	456

Data Sheet

A

B

1	123,456,789	RED
2	8	BLUE

3

752,451

YELLOW

Data Sheet2

	A	B
1	652,458,752	ORANGE
2	4	GREEN

 

[DanteAmor]

Is the data as texts inside the cell?

123,456,789
8
752,451

 

[rambojr]

it is categorized as "General"

 

[DanteAmor]

But that number how it looks, as text or as a number.

________________________________________________________________________-

By the way, the formula does not look for 456 inside the cell, I do not understand how the RED color returns to you.

varios 11jun2020.xlsm
	B	C
1	456	#N/A
Lookup Sheet

Cell Formulas
Range	Formula
C1	C1	=VLOOKUP(B1,'Data Sheet'!A1:B3,2,0)

 

[rambojr]

IT shows as text

the numbers in the data sheet have a space after the comma line 123, 456, 789

 

[rambojr]

Sorry I pasted the wrong formula it should be =IFERROR(VLOOKUP("*"&B$1&"*",'Data Sheet'!$A$1:$B$2,2,FALSE),IFERROR(VLOOKUP("*"&B$1&"*",'Data Sheet2'!$A$1:$B$3,2,FALSE),"nothing found"))

 

[DanteAmor]

Is an array formula

Dante Amor
	B	C
1	451	YELLOW
Lookup Sheet

Cell Formulas
Range	Formula
C1	C1	=TRIM(SUBSTITUTE(IFERROR(VLOOKUP("*, "&B1&",*",", "&'Data Sheet'!A1:B3&",",2,0),IFERROR(VLOOKUP("*, "&B1&",*",", "&'Data Sheet2'!A1:B3&",",2,0),"nothing found")),",",""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dante Amor
	A	B
1	123, 456, 789	RED
2	8	BLUE
3	752, 451	YELLOW
Data Sheet

Dante Amor
	A	B
1	652, 458, 752	ORANGE
2	4	GREEN
Data Sheet2

 

[rambojr]

Thank you DanteAmor that did the trick.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.