Column with Partial Match to Another Column to Give Desired Output

[kkmmaahh1]

Hey all!

I'm trying to search sentences with construction activities for a specific part of that sentence, and give the corresponding output. Here is an example from the file I am working on:

Column A:
[TABLE="width: 416"]
<colgroup><col width="416" style="width:312pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 416"]LEVEL 04 - INSTALL STAIRS
[TABLE="width: 416"]
<colgroup><col width="416" style="width:312pt"> </colgroup><tbody>[TR]
[TD="width: 416"]LEVEL 07 - COLUMNS & SHEARWALLS/ STRIP PERIMETER FORM
[TABLE="width: 416"]
<colgroup><col width="416" style="width:312pt"> </colgroup><tbody>[TR]
[TD="width: 416"]LEVEL 07 - STRIP FORMS
[TABLE="width: 416"]
<colgroup><col width="416" style="width:312pt"> </colgroup><tbody>[TR]
[TD="width: 416"]LEVEL 01 - POUR 1

Column B: Column C:
pour 03
strip forms 05
install stairs 03
shearwalls 05

I have looked at endless threads to no avail; these two formulas came closest:

=INDEX(C1:C4,MATCH(A1,B1:B4,-1) :: this somehow gave 05 for all results

=INDEX(C1:C4,MATCH("*"&A1&"*",B1:B4,-1) :: This gave error #NA

I've tried changing the -1 in both of those to 0 and still got error #NA

I've also tried:
=VLOOKUP(A1,B1:C4,2,TRUE)
and = VLOOKUP("*"&A1&"*",B1:C4,2,TRUE), both to no avail.

Please help as I am slowly loosing my mind trying to figure this out! Thanks in advance!



[/TD]
[/TR]
[TR]
[TD="width: 416"][/TD]
[/TR]
[TR]
[TD="width: 416"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[kkmmaahh1]

EDIT: Columns B and C are:

B:
pour
strip forms
install stairs
shearwalls

C:
03
05
05
03

 

[inactiveuserps07]

It's unclear to me still what you are trying to do.

In your second post, you said:

C:
03
05
05
03

Is that the result you WANT? If so, where do those numbers come from?

Or are you saying this is what you are currently GETTING, but you WANT:

C:
01
07
04
07

... which are the level numbers that correspond to where the keywords in Column B were found?

 

[hiker95]

kkmmaahh1,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[kkmmaahh1]

It's unclear to me still what you are trying to do.

In your second post, you said:

C:
03
05
05
03

Is that the result you WANT? If so, where do those numbers come from?

Or are you saying this is what you are currently GETTING, but you WANT:

C:
01
07
04
07

... which are the level numbers that correspond to where the keywords in Column B were found?

My apologies for the poorly written post - it is my first and I was running pretty low on energy. The follow-up reply I posted earlier about column C was wrong anyway (my bad). Let me clarify:

The example above shows what I was trying to write out. I need my output in column D as shown. The numbers in column C have nothing to do with the levels in column A. Rather, column C corresponds to column B. Namely, if a cell in column A contains the word(s) in column B, then column D should show column B's corresponding value in column C (wordy, I know). The final result should be as follows

D1, which shows the value relevant to A1, shows 03 because B3 (install stairs) corresponds to C3 (03). D2 shows 05 because shearwalls (B4) corresponds to C4 (05). D3 shows shows 05 because B2 (strip forms) corresponds to C2 (05).

Unfortunately, the formula I am using (dragged down D) just gives me an output of "05" for all rows.

I hope things are clearer now. Thanks for bearing with me

 

[hiker95]

kkmmaahh1,

You have posted images that we can not use/copy into a worksheet.

This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually?????

Maybe someone else on MrExcel will be able to assist you.

 

[Marcelo Branco]

Try

D1 copied down
=LOOKUP(9.99E+307,SEARCH($B$1:$B$4,A1),$C$1:$C$4)

About the formula: see the excellent explanation provided by Aladin Akyurek at
https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

M.

 

[kkmmaahh1]

Try

D1 copied down
=LOOKUP(9.99E+307,SEARCH($B$1:$B$4,A1),$C$1:$C$4)

About the formula: see the excellent explanation provided by Aladin Akyurek at
https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

M.

Worked like a charm! Your help is much appreciated, and thanks for providing the explanation as well!