finding text that is a substring of text of a cell in a column

[whodunit]

[FONT=&quot]I have a test value - call it AB - that I want to find in a table in a different sheet then find the corresponding value 2 or 3 rows over.
[/FONT]
[FONT=&quot]Here is an example:
[/FONT]
[FONT=&quot]Tab 1
[/FONT]
[TABLE="class: k-table, width: 980"]
<tbody>[TR]
[TD]AA
[/TD]
[TD]Meaningless text
[/TD]
[TD]random number
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]Meaningless Text
[/TD]
[TD]random number
[/TD]
[/TR]
[TR]
[TD]AC
[/TD]
[TD]Meaningless text
[/TD]
[TD]random number
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
[/FONT]
[FONT=&quot]Tab 2
[/FONT]
[TABLE="class: k-table, width: 980"]
<tbody>[TR]
[TD]1
[/TD]
[TD]AA,BA,CA
[/TD]
[TD]50
[/TD]
[TD]1.5
[/TD]
[TD]Alpha
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]AB,BB,CB
[/TD]
[TD]100
[/TD]
[TD]2.5
[/TD]
[TD]Beta
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]AC,BC,CC
[/TD]
[TD]150
[/TD]
[TD]3.5
[/TD]
[TD]Gamma
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]I want to find from tab 1 the value in cell A2 on Tab2 in column B then return the value 2 columns to the right. In this example, find "AB" (text will change so should be cell reference but no big deal there) in the table in Tab 2 then return "2.5" which is 2 cells to the right.
[/FONT]
[FONT=&quot]There's more I want to do but this is the core of all of it.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]here's a sample from a real table that I might use. Input will be from column A...so if I put in the cell in tab 1 that I want to find the value associated with "D" in column A which exists as a substring of C, D, I, R), I want to find 2.00 (2 columns over) or whatever value I decide I want.
[/FONT]
[FONT=&quot]Column A will likely be unsorted. There is not a 100% guarantee it might exist. For example (this is related to airline fare classes), a fare class of "X" (hypothetical in this particular case) might exist but doesn't give earnings because it's not in the table at all so I'd like to return 0.
[/FONT]
[FONT=&quot]
[/FONT]

<tbody style="box-sizing: border-box; margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: text-center, align: center"][/TD]
[TD="class: text-center, align: center"]50%
[/TD]
[TD="class: text-center, align: center"]3.00
[/TD]
[TD="class: text-center, align: center"]30%
[/TD]
[TD="class: text-center, align: center"]1.00
[/TD]

[TD="class: text-center, align: center"]A*
[/TD]
[TD="class: text-center, align: center"]100%
[/TD]
[TD="class: text-center, align: center"]50%
[/TD]
[TD="class: text-center, align: center"]2.00
[/TD]
[TD="class: text-center, align: center"]30%
[/TD]
[TD="class: text-center, align: center"]1.00
[/TD]

[TD="class: text-center, align: center"]J
[/TD]
[TD="class: text-center, align: center"]100%
[/TD]
[TD="class: text-center, align: center"]25%
[/TD]
[TD="class: text-center, align: center"]3.00
[/TD]
[TD="class: text-center, align: center"]25%
[/TD]
[TD="class: text-center, align: center"]1.00
[/TD]

[TD="class: text-center, align: center"]C, D, I, R
[/TD]
[TD="class: text-center, align: center"]100%
[/TD]
[TD="class: text-center, align: center"]25%
[/TD]
[TD="class: text-center, align: center"]2.00
[/TD]
[TD="class: text-center, align: center"]25%
[/TD]
[TD="class: text-center, align: center"]1.00
[/TD]

[TD="class: text-center, align: center"]W
[/TD]
[TD="class: text-center, align: center"]100%
[/TD]
[TD="class: text-center, align: center"]10%
[/TD]
[TD="class: text-center, align: center"]1.50
[/TD]
[TD="class: text-center, align: center"]22%
[/TD]
[TD="class: text-center, align: center"]1.00
[/TD]

</tbody>

[FONT=&quot]Make sense? I've been playing around with find, search, vlookup, match, index, substring, etc, etc, but I'm stuck. Help a newbie?
[/FONT]

 

[jtakw]

Hi,

You can do a VLOOKUP using wildcard match:

Book1
	A	B	C	D	E	F
1	Lookup Value	Result
2	D	0.25
3
4
5		50%	3	30%	1
6	A*	100%	50%	2	30%	1
7	J	100%	25%	3	25%	1
8	C, D, I, R	100%	25%	2	25%	1
9	W	100%	10%	1.5	22%	1
Sheet400

Cell Formulas
Range	Formula
B2	=VLOOKUP("*"&A2&"*",A5:C9,3,0)

 

[whodunit]

Hi,

You can do a VLOOKUP using wildcard match:

	A	B	C	D	E	F
Lookup Value	Result
D
A*
J
C, D, I, R
W

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]100%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]100%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]100%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]100%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet400​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=VLOOKUP("*"&A2&"*",A5:C9,3,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thank you for the fast reply!
not sure why I couldn't figure that out. separate issue
an extension of my question - I hope that's ok....part of the same formula.
[FONT=&quot]I have this workbook with each tab containing a table like the above. I'm creating a tracking worksheet to list out flights and calculate miles, earnings, etc. So there's a main "reporting" tab where I will enter the details of a flight which will contain airline code ("AA" or "BA" for example), fare class (the letters above such as "Q" or "J"), and other info. The miles are screen-scraped from a mileage calculating flight then some formula is applied to those miles based on that fare class.[/FONT]
[FONT=&quot]So, if the fare class is "B," you might find a 0.5 in the table on the "AA" tab and maybe 1.0 on the "BA" tab (these are not real numbers, just examples).[/FONT]
[FONT=&quot]So I want to do on log tab:[/FONT]
[TABLE="width: 924"]
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">[TR]
[TD]Airline[/TD]
[TD]city pair[/TD]
[TD]miles[/TD]
[TD]fare class[/TD]
[TD]earned miles[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]MCO-LAX[/TD]
[TD]screen scrape formula[/TD]
[TD]O[/TD]
[TD]= (your formula)
[/TD]
[/TR]
[TR]
[TD]JL[/TD]
[TD]LAX-NRT
[/TD]
[TD]screen scrape formula[/TD]
[TD]N[/TD]
[TD]= (your formula)[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]one tab will be called "AA"[/FONT]
[FONT=&quot]another tab will be called "BA"[/FONT]
[FONT=&quot]another will be "JL"[/FONT]
[FONT=&quot]I will enter the airline code as above say "AA" or "JL" and I'm hoping that the formula under earned miles will:[/FONT]
[FONT=&quot]lookup the table (in my first post on every tab with different content but same columns, etc)...[/FONT]
[FONT=&quot]=IFERROR(VLOOKUP(fare_class_cell, Airline_code!$B$2:$E$100,2,FALSE),"CAN'T FIND")[/FONT]
[FONT=&quot]or[/FONT]
[FONT=&quot]=IFERROR(VLOOKUP(D2,D1!$B$2:$E$100,2,FALSE),"boo").[/FONT]
[FONT=&quot]I hope this is clear.[/FONT]