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

[mmisky87]

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)

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 327"]
<colgroup><col width="327"></colgroup><tbody>[TR]
[TD="class: xl65, width: 327"]PULLED
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]PAY
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 327"]
<colgroup><col width="327"></colgroup><tbody>[TR]
[TD="class: xl65, width: 327"](BAR) 2002 FORD F250SD PICKUP Speedometer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 327"]
<colgroup><col width="327"></colgroup><tbody>[TR]
[TD="class: xl65, width: 327"](BAR) 2014 MALIBU L Taillight[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 327"]
<colgroup><col width="327"></colgroup><tbody>[TR]
[TD="class: xl65, width: 327"](BAR) 1998 BMW 528i Right Fender[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 327"]
<tbody>[TR]
[TD="class: xl63, width: 327"]
Sheet2 (Pay)
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 116"]
<colgroup><col width="116"></colgroup><tbody>[TR]
[TD="class: xl65, width: 116"]PART TYPE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] PAY
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<colgroup><col width="116"></colgroup><tbody>[TR]
[TD="class: xl65, width: 116"]Speedometer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]$3.00
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<colgroup><col width="116"></colgroup><tbody>[TR]
[TD="class: xl65, width: 116"]L Taillight[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]$5.00
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<colgroup><col width="116"></colgroup><tbody>[TR]
[TD="class: xl65, width: 116"]Right Fender[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]$7.00
[/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[/TR]
</tbody>[/TABLE]

 

[azumi]

SOmething like this?

https://simpan.ugm.ac.id/s/mSPIM2dZjIUs6PV

 

[mmisky87]

That works, but what about if sheet1 called "pulled" constantly changes? Here is an example of a little larger sample:

https://drive.google.com/file/d/0B_nBGD7LIRsXRjBBOWtlZy16ZTg/view?usp=sharing

 

[Aladin Akyurek]

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]

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]

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]

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!

 

[Aladin Akyurek]

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!

Sounds graet. Thanks for the update.