Search Cells for Partial Text in One Sheet and Return Value from Seperate Sheet

mmisky87

New Member
Joined
Dec 17, 2016
Messages
4
I have played around with a bunch of search functions, but I keep falling short. I think this is an easy question, but I am just at my end of my capabilities.

I have raw data in a sheet1 called "pulled". Then a sheet2 called "pay".

The end result I need is a formula in the "pay" tab of Sheet1 to show the pay for a particular item under the B column. For example, the formula in B2 would equal $3. The formula in B5 would equal $7.

The challenge I have is the data in the sheet1 A2:A5 will always vary with years and models, but the ending names (part types) will always match the data in the sheet2 pay.

Basically, I need to have a formula that looks at the data in sheet1 A column, searches the text in sheet2 A column and return the value in sheet 1 B column.

Sheet1 (PULLED)

PULLED

<colgroup><col width="327"></colgroup><tbody>
</tbody>
PAY

<colgroup><col width="64"></colgroup><tbody>
</tbody>
(BAR) 2002 FORD F250SD PICKUP Speedometer

<colgroup><col width="327"></colgroup><tbody>
</tbody>
(BAR) 2014 MALIBU L Taillight

<colgroup><col width="327"></colgroup><tbody>
</tbody>
(BAR) 1998 BMW 528i Right Fender

<colgroup><col width="327"></colgroup><tbody>
</tbody>

<tbody>
</tbody>


Sheet2 (Pay)
PART TYPE

<colgroup><col width="116"></colgroup><tbody>
</tbody>
PAY

<colgroup><col></colgroup><tbody>
</tbody>
Speedometer

<colgroup><col width="116"></colgroup><tbody>
</tbody>
$3.00
L Taillight

<colgroup><col width="116"></colgroup><tbody>
</tbody>
$5.00
Right Fender

<colgroup><col width="116"></colgroup><tbody>
</tbody>
$7.00

<tbody>
</tbody>




<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Name the range housing the part types PART and the range of corresponding pays PAY. The data on Sheet2 must be sorted in ascending order on the PART column (column A).

In B2 of Sheet1 (pulled) just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(PART,$A2),PAY)

In order to check whether the formula behaves in the intended way, in C2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(PART,$A2),PART)
 

mmisky87

New Member
Joined
Dec 17, 2016
Messages
4

ADVERTISEMENT

Hmm. I'm not getting it to work. I tried it in googledoc&excel.

For simplicity, I am naming sheet1 "PULLED", A col "Pulled", B col "Com". Naming sheet 2 "PAYSHEET", A col "Part", B col "Pay".

I may have neglected one bit of information, sheet 2 "paysheet" will always stay the same, but sheet 1 "pulled) will vary in data and may contain duplicates. Can you look at my sheet Aaladin?

https://docs.google.com/spreadsheets/d/1ahE9i-alQxPeO8LruhyixTcNsBkmZfOCJRVwolxmbIo/edit?usp=sharing


 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hmm. I'm not getting it to work. I tried it in googledoc&excel.

For simplicity, I am naming sheet1 "PULLED", A col "Pulled", B col "Com". Naming sheet 2 "PAYSHEET", A col "Part", B col "Pay".

I may have neglected one bit of information, sheet 2 "paysheet" will always stay the same, but sheet 1 "pulled) will vary in data and may contain duplicates. Can you look at my sheet Aaladin?

https://docs.google.com/spreadsheets/d/1ahE9i-alQxPeO8LruhyixTcNsBkmZfOCJRVwolxmbIo/edit?usp=sharing



What follows is done in Excel. Apparently I cannot upload the reworked file to Google Drive in order to open in Google Sheets...

Define Lrow in Name Manager as referring to:

=MATCH(REPT("z",255),PAYSHEET!$A:$A)

Define PART in Name Manager as referring to:

=PAYSHEET!$A$2:INDEX(PAYSHEET!$A:$A,Lrow)

Define PAY in Name Manager as referring to:

=PAYSHEET!$B$2:INDEX(PAYSHEET!$B:$B,Lrow)

The foregoing makes the set up dynamic, self-adjusting to the PAYSHEET data. Note that A:B of PAYSHEET must be sorted in ascending order on column A.

In B2 of PULLED enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(PART,$A2),PAY)

For check, in C2 of PULLED enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(PART,$A2),PART)

See: https://dl.dropboxusercontent.com/u/65698317/mmisky87 HELP.xlsx

Hope you can read this also in Google Sheets.
 

mmisky87

New Member
Joined
Dec 17, 2016
Messages
4
That is working perfect. Thank you for the help. I'm going to be studying this further. It looks like you found the simplest way!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top