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]

AA	Meaningless text	random number
AB	Meaningless Text	random number
AC	Meaningless text	random number

<tbody>
</tbody>

[FONT=&quot]
[/FONT]
[FONT=&quot]Tab 2
[/FONT]

1	AA,BA,CA	50	1.5	Alpha
2	AB,BB,CB	100	2.5	Beta
3	AC,BC,CC	150	3.5	Gamma

<tbody>
</tbody>

[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]

	50%	3.00	30%	1.00
A*	100%	50%	2.00	30%	1.00
J	100%	25%	3.00	25%	1.00
C, D, I, R	100%	25%	2.00	25%	1.00
W	100%	10%	1.50	22%	1.00

<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;">
</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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet400​

Worksheet Formulas Cell Formula B2 =VLOOKUP("*"&A2&"*",A5:C9,3,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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]

Airline	city pair	miles	fare class	earned miles
AA	MCO-LAX	screen scrape formula	O	= (your formula)
JL	LAX-NRT	screen scrape formula	N	= (your formula)

<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>

[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]