Specific Vlookup question - matching a partial to an exact string.

[brockmoller]

I have sheet 1 with one column of values like this:

F14BLINKIN-DW

F14BRAVE-DW

F14JORJALG-DW

<tbody>
</tbody>

I have sheet 2 with 2 columns of values like this:

BLINKIN	$125.00​
Deeter	$150.00​
Lucy	$130.00​

<tbody>
</tbody>

I need to make a column B in sheet 1 that grabs the $ values from column B in sheet 2.

I need a formula to put in column B of sheet 1 that will look up the value "BLINKIN" from column A in sheet 2 and correspond that to the same partial string in sheet 1 (F14BLINKIN-DW contains "BLINKIN") thus giving me $125 in column B of sheet 1.

I have figured out how to do this in reverse (I can get a value from column B in sheet 1 and put that in column C of sheet 2 using this formula):

IF("*"&A4&"*"="","",VLOOKUP("*"&A4&"*",flatfile::Table 1::$A$2:$C$219,2,FALSE))

I need to know how to do this in reverse, basically. Any ideas would be greatly appreciated!! Thank you!!

 

[platonic567]

Based on your sample data without headers, try below formula:

=LOOKUP(2,1/(SEARCH(Sheet2!$A$1:$A$3,Sheet1!A1)),Sheet2!$B$1:$B$3)

 

[brockmoller]

Based on your sample data without headers, try below formula:

=LOOKUP(2,1/(SEARCH(Sheet2!$A$1:$A$3,Sheet1!A1)),Sheet2!$B$1:$B$3)

Hmmmm - that didn't seem to work. Wouldn't I need a VLOOKUP? I tried with VLOOKUP instead but to no avail.

 

[platonic567]

My formula uses LOOKUP function so no point changing it to VLOOKUP.

Could you perhaps give more details on what happened when you entered the formula?

Did you try it on sample data you have provided or tried to modify ranges etc?

 

[brockmoller]

Sheet 1 is called "flatfile" and includes lines 2-219. Sheet two is called "prices" and includes lines 2-56. They are two different tabs on the same workbook. Sheet 1 has the info such as "F14ALTER-VN" and sheet 2 has the info such as "ALTER". I'm working in sheet 1 and need to search sheet 2 and bring info from column b in sheet 2 into column b in sheet 1 wanting excel to match up F14ALTER-VN and ALTER. When I tried to make any alterations, I only got syntax error. Hope that extra info helps! Thank you so much.

 

[platonic567]

See if below works:

=LOOKUP(2,1/(SEARCH(prices!$A$2:$A$56,flatfile!A2)),prices!$B$2:$B$56)