Use search from a list.

[Metaripley]

I want to use the search function but from a list.

Ive got a column with a string of text. (A)
And Ive got a column with a short string of text. (B)
And a column with a category corresponding with the short string. (C)

Now I want to use the list of short string to find the same in the long string list. (D)

List (B/C) contain more then 64 rows.
I used a consecutive If function but it cannot contain more then 64.

=IF(ISNUMBER(SEARCH(_18,P5)),_18.1,IF(ISNUMBER(SEARCH(_19,P5)),_19.1,IF(ISNUMBER(SEARCH(_20,P5)),_20.1,IF(ISNUMBER(SEARCH(_21,P5)),_21.1,IF(ISNUMBER(SEARCH(_22,P5)),_22.1,IF(ISNUMBER(SEARCH(_23,P5)),_23.1,IF(ISNUMBER(SEARCH(_24,P5)),_24.1,IF(ISNUMBER(SEARCH(_25,P5)),_25.1,IF(ISNUMBER(SEARCH(_26,P5)),_26.1,IF(ISNUMBER(SEARCH(_27,P5))


Can I combine a MATCH/SEARCH function in any way maybe?

A	B	C	D
AUTOSHOP VAN DENHEUSDEN-ZO	Total	Car	Car
APOTHEEK DEMEULEBERINGEN	Stockpaal	Home
BVBA T HOF BERINGEN	Park	Car	Dokter
AMERIKAAMSE STOCPAAL	Auto	Car	Home
DR. JOHAN VANDERLUMMEN	Hof	Dokter	Dokter
TOTAL NB000629 TTESSENDERL	vanderlummen	Dokter	Car
Q PARK ASTRIDPLEIN			Car
P - DEN HAAG 48068
AUTOSHOP VAN DENHEUSDEN-ZO			Car

<tbody>
</tbody>

 

[DanteAmor]

With the following array formula, you can get the category data.
If a string has more than one category, it will bring you the one in the row below. Check my example of cell A6.


Change in the formula, number 2 by the row number where your data begins. Change the number 7 to the row number where your List(B/C) relationship ends


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:231.92px;" /><col style="width:116.91px;" /><col style="width:96px;" /><col style="width:121.66px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">STRING</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">SHORT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">CATEGORY</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AUTOSHOP VAN DENHEUSDEN-ZO</td><td >Total</td><td >Car</td><td >Car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >APOTHEEK DEMEULEBERINGEN</td><td >Stockpaal</td><td >Home</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BVBA T HOF BERINGEN</td><td >Park</td><td >Car</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >AMERIKAAMSE STOCkPAAL</td><td >Auto</td><td >Car</td><td >Home</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >AMERIKAAMSE STOCkPAAL Hof</td><td >Hof</td><td >Dokter</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >DR. JOHAN VANDERLUMMEN</td><td >vanderlummen</td><td >Dokter</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >TOTAL NB000629 TTESSENDERL</td><td > </td><td > </td><td >Car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Q PARK ASTRIDPLEIN</td><td > </td><td > </td><td >Car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >P - DEN HAAG 48068</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >AUTOSHOP VAN DENHEUSDEN-ZO</td><td > </td><td > </td><td >Car</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D2</td><td >{=IFERROR(INDEX($C$2:$C$7,SUMPRODUCT(MAX(ISNUMBER(SEARCH($B$2:$B$7,A2))*(ROW($B$2:$B$7))))-ROW($B$2)+1),"")}</td></tr></table></td></tr></table>



Let me know if you have any doubt.

 

[DanteAmor]

Array formula

{=IFERROR(INDEX($C$2:$C$7,SUMPRODUCT(MAX(ISNUMBER(SEARCH($B$2:$B$7,A2))*(ROW($B$2:$B$7))))-ROW($B$2)+1),"")}


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

-----

If a string does not contain more than one short text, then you can use this "regular" formula:

=IFERROR(INDEX($C$2:$C$7,SUMPRODUCT(ISNUMBER(SEARCH($B$2:$B$7,A2))*(ROW($B$2:$B$7)))-ROW($B$2)+1),"")

 

[Metaripley]

Let me know if you have any doubt.

Thank you so much!
I would have come up with this myself.
Works like a charm.

 

[Metaripley]

One remark:

When I put Column B in row 1 instead of 2 or lower, It does not give me "" as a result but the first entry of column B (CAR) in every cell that should be blank.

 

[DanteAmor]

Thank you so much!
I would have come up with this myself.
Works like a charm.

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

 

[DanteAmor]

One remark:

When I put Column B in row 1 instead of 2 or lower, It does not give me "" as a result but the first entry of column B (CAR) in every cell that should be blank.

I do not understand what you mean by row 1 or lower, there are no rows less than 1.

 

[Aryatect]

Hi,

Another option:

Book1
	A	B	C	D
1	STRING	SHORT	CATEGORY	RESULT
2	AUTOSHOP VAN DENHEUSDEN-ZO	Total	Car	Car
3	APOTHEEK DEMEULEBERINGEN	Stockpaal	Home
4	BVBA T HOF BERINGEN	Park	Car	Dokter
5	AMERIKAAMSE STOCkPAAL	Auto	Car	Home
6	AMERIKAAMSE STOCkPAAL Hof	Hof	Dokter	Home
7	DR. JOHAN VANDERLUMMEN	vanderlummen	Dokter	Dokter
8	TOTAL NB000629 TTESSENDERL			Car
9	Q PARK ASTRIDPLEIN			Car
10	P - DEN HAAG 48068
11	AUTOSHOP VAN DENHEUSDEN-ZO			Car
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($C$2:$C$11,AGGREGATE(15,6,IF(($B$2:$B$11<>"")*SEARCH($B$2:$B$11,A2),ROW($A$2:$A$11)-ROW($A$2)+1),1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in D2 down to D11

 

[DanteAmor]

One remark:

When I put Column B in row 1 instead of 2 or lower, It does not give me "" as a result but the first entry of column B (CAR) in every cell that should be blank.

You mean to remove the headers.
I like more with headers.
But here's the array formula to start in row 1. It's even a shorter formula than my previous formula.



<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:257.58px;" /><col style="width:116.91px;" /><col style="width:96px;" /><col style="width:121.66px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">STRING</td><td >park</td><td >CAR</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AUTOSHOP VAN DENHEUSDEN-ZO</td><td >Total</td><td >Car</td><td >Car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >APOTHEEK DEMEULEBERINGEN</td><td >Stockpaal</td><td >Home</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BVBA T HOF BERINGEN</td><td >SW</td><td >Car</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >AMERIKAAMSE STOCkPAAL</td><td >Auto</td><td >Car</td><td >Home</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >AMERIKAAMSE STOCkPAAL Hof</td><td >Hof</td><td >Dokter</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >DR. JOHAN VANDERLUMMEN</td><td >vanderlummen</td><td >Dokter</td><td >Dokter</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >TOTAL NB000629 TTESSENDERL</td><td > </td><td > </td><td >Car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Q PARK ASTRIDPLEIN</td><td > </td><td > </td><td >CAR</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >P - DEN HAAG 48068</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >AUTOSHOP VAN DENHEUSDEN-ZO</td><td > </td><td > </td><td >Car</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >D2</td><td >{=IFERROR(OFFSET($C$1,MAX((ISNUMBER(SEARCH($B$1:$B$7,A2)))*(ROW($B$1:$B$7)))-1,0),"")}</td></tr></table></td></tr></table>