Show different values based on the result using nested IF and VLOOKUP formula

[purevtsagaan]

	A	B	C
1	clothes	transportation	drinks
2	pants	car	coffee
3	shirt	train	tea
4	sweater	bicycle	juice
5	tie	plane	milk

<tbody>
</tbody>

Hello everyone,
I have been trying so had to make this work.
I want to return "category 1" if the text is found in column A, "category 2"if it is found n column b, "category 3" If it is found in column c, if not found in any column "category 4".
Please help here is sheet and formula, thanks

=IF(SUBSTITUTE(A1, "-", "")=VLOOKUP(SUBSTITUTE(A1,"-",""),sheet1!$A$2:$A$6,1,FALSE),
"CATEGORY 1",
IF(SUBSTITUTE(A1, "-", "")=VLOOKUP(SUBSTITUTE(A1, "-", ""),sheet1!$B$2:$B$6,2,FALSE),
"CATEGORY 2",
IF(SUBSTITUTE(A1, "-", "")=VLOOKUP(SUBSTITUTE(A1, "-", ""),sheet1!$C$2:$C$6,3,FALSE),
"CATEGORY 3",
"Category 4")
)
)

PS: first if statement works but 2nd and 3rd ones do not. Please help me by fixing. Thanks!

 

[Aladin Akyurek]

What text are we looking for?

 

[purevtsagaan]

Sorry, the text t test is here,

BICYCLE

soc-ks

PA-NTS

pla-ne

ca-r

milk

juice

tea

cof-fee

<tbody>
</tbody>

So I want the function to sort these texts into 4 different categories based on their types.

 

[Aladin Akyurek]

Sorry, the text t test is here,

BICYCLE

soc-ks

PA-NTS

pla-ne

ca-r

milk

juice

tea

cof-fee

<tbody>
</tbody>

So I want the function to sort these texts into 4 different categories based on their types.

Does the hyphen in say soc-ks really exist?

 

[purevtsagaan]

yup, the hyphen exists.
the substitute removes he hyphens.

 

[Aladin Akyurek]

yup, the hyphen exists.
the substitute removes he hyphens.

clothes	transportation	drinks				#List#	Category
pants	car	coffee				BICYCLE	2
shirt	train	tea				soc-ks	4
sweater	bicycle	juice				PA-NTS	1
tie	plane	milk				pla-ne	2
						ca-r	2
						milk	3
						juice	3
						tea	3
						cof-fee	3

<tbody>
</tbody>

H2, control+shift+enter, not just enter, and copy down:

=MIN(4,MIN(IF($A$2:$C$5=SUBSTITUTE(G2,"-",""),
  COLUMN($A$2:$C$5)-MIN(COLUMN($A$2:$C$5))+1,9.99E+307)))

 

[purevtsagaan]

Aladin thank you so much it works,
But could you explain how above code works step by step if it is not too much.
I am in debt to you.

 

[Aladin Akyurek]

Aladin thank you so much it works,

You are welcome.

But could you explain how above code works step by step if it is not too much.
I am in debt to you.

The IF bit in:

=MIN(4,MIN(IF($A$2:$C$5=SUBSTITUTE(G2,"-",""),COLUMN($A$2:$C$5)-MIN(COLUMN($A$2:$C$5))+1,9.99E+307)))

returns the relative column number when the data equals the #List# value of interest (hyphens removed if any), otherwise the Excel's big number. That is, the formula maps the relative column number one to one to the categories as you specified.

The valuation occurs successively as follows:

==>

=MIN(4,MIN(IF({FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FALSE},COLUMN($A$2:$C$5)-MIN(COLUMN($A$2:$C$5))+1,9.99E+307)))

==>

=MIN(4,MIN(IF({FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FALSE},{1,2,3},9.99E+307)))

==> (note the big number for each FALSE)

=MIN(4,MIN({9.99E+307,9.99E+307,9.99E+307;9.99E+307,9.99E+307,9.99E+307;9.99E+307,2,9.99E+307;9.99E+307,9.99E+307,9.99E+307}))

==>

=MIN(4,2)

==>

2

which is the desired outcome for BICYCLE.

Hope this helps.