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>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,210
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,041
Members
430,257
Latest member
Todor T

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