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

[purevtsagaan]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]clothes
[/TD]
[TD]transportation
[/TD]
[TD]drinks
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]pants
[/TD]
[TD]car
[/TD]
[TD]coffee
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]shirt
[/TD]
[TD]train
[/TD]
[TD]tea
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]sweater
[/TD]
[TD]bicycle
[/TD]
[TD]juice
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]tie
[/TD]
[TD]plane
[/TD]
[TD]milk
[/TD]
[/TR]
</tbody>[/TABLE]










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,
[TABLE="width: 62"]
<tbody>[TR]
[TD="width: 83, bgcolor: transparent"]BICYCLE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]soc-ks[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PA-NTS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pla-ne[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ca-r[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]milk[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]juice[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]tea[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]cof-fee[/TD]
[/TR]
</tbody>[/TABLE]

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,
[TABLE="width: 62"]
<tbody>[TR]
[TD="width: 83, bgcolor: transparent"]BICYCLE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]soc-ks[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PA-NTS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pla-ne[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ca-r[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]milk[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]juice[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]tea[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]cof-fee[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]clothes[/TD]
[TD="width: 119"]transportation[/TD]
[TD="width: 64"]drinks[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 72"]#List#[/TD]
[TD="width: 71"]Category[/TD]
[/TR]
[TR]
[TD="width: 64"]pants[/TD]
[TD="width: 119"]car[/TD]
[TD="width: 64"]coffee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]BICYCLE[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="width: 64"]shirt[/TD]
[TD="width: 119"]train[/TD]
[TD="width: 64"]tea[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]soc-ks[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="width: 64"]sweater[/TD]
[TD="width: 119"]bicycle[/TD]
[TD="width: 64"]juice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]PA-NTS[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64"]tie[/TD]
[TD="width: 119"]plane[/TD]
[TD="width: 64"]milk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]pla-ne[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]ca-r[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]milk[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]juice[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]tea[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 72, bgcolor: transparent"]cof-fee[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

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.