Lookup Help

[MrOaks]

Hi,

I have a spreadsheet with 2 tabs.

1. 1 tab contains multiline values (column A) and it's master name (column B)
2. The other tab has split out those multiline values into separate rows

What I now want to do is look up the master name in the split out sheet but I can't figure out a way to lookup against the multiline cells.

Example multiline sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Process1
Process2
Process3[/TD]
[TD]Master1[/TD]
[/TR]
[TR]
[TD]Process4
Process5[/TD]
[TD]Master2[/TD]
[/TR]
</tbody>[/TABLE]

Example split out sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Process1[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Process2[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Process3[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Process4[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Process5[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]
So now, I want to look up the master name (??) on value process against where it finds it in the multiline cell.

Can this be done?

Thanks

 

[steve the fish]

Im thinking there may be better ways but this works. Put first table in A1:B5 and eg Process2 in D1:

=LOOKUP(2,1/($B$1:INDEX($B$1:$B$1000,MATCH(D1,$A$1:$A$1000,0))<>""),$B$1:INDEX($B$1:$B$1000,MATCH(D1,$A$1:$A$1000,0)))

 

[MrOaks]

Im thinking there may be better ways but this works. Put first table in A1:B5 and eg Process2 in D1:

=LOOKUP(2,1/($B$1:INDEX($B$1:$B$1000,MATCH(D1,$A$1:$A$1000,0))<>""),$B$1:INDEX($B$1:$B$1000,MATCH(D1,$A$1:$A$1000,0)))

Thanks, but I can't get it to work with my data across 2 different sheets.

The multiline cells to lookup against is SHEETA and the lookup value is in SHEETB.

When I amend the above to reflect that, it doesn't work.

 

[steve the fish]

Lets see the amended formula you have.

 

[AlanY]

Thanks, but I can't get it to work with my data across 2 different sheets.

The multiline cells to lookup against is SHEETA and the lookup value is in SHEETB.

When I amend the above to reflect that, it doesn't work.

this is how Steve's post#2 work

Book1
	A	B
1	Process1	Master1
2	Process2
3	Process3
4	Process4	Master2
5	Process5
SHEETA

Book1
	A	B
1	Process1	Master1
2	Process2	Master1
3	Process3	Master1
4	Process4	Master2
5	Process5	Master2
SHEETB

Cell Formulas
Range	Formula
B1	=LOOKUP(2,1/(SHEETA!$B$1:INDEX(SHEETA!$B$1:$B$1000,MATCH(A1,SHEETA!$A$1:$A$1000,0))<>""),SHEETA!$B$1:INDEX(SHEETA!$B$1:$B$1000,MATCH(A1,SHEETA!$A$1:$A$1000,0)))

 

[MrOaks]

this is how Steve's post#2 work

	A	B
Process1	Master1
Process2
Process3
Process4	Master2
Process5

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

</tbody>

SHEETA​

	A	B
Process1	Master1
Process2	Master1
Process3	Master1
Process4	Master2
Process5	Master2

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

SHEETB​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=LOOKUP(2,1/(SHEETA!$B$1:INDEX(SHEETA!$B$1:$B$1000,MATCH(A1,SHEETA!$A$1:$A$1000,0))<>""),SHEETA!$B$1:INDEX(SHEETA!$B$1:$B$1000,MATCH(A1,SHEETA!$A$1:$A$1000,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Ah, I see now. That makes sense.

However my data looks a bit different.

SheetA
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Process1
Process2
process3[/TD]
[TD="align: center"]Master1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Process4
Process5[/TD]
[TD="align: center"]Master2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Process6
Process7[/TD]
[TD="align: center"]Master3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Process8[/TD]
[TD="align: center"]Master4[/TD]
[/TR]
</tbody>[/TABLE]


SheetB
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Process1[/TD]
[TD="align: center"]NEED to LOOKUP[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Process2[/TD]
[TD="align: center"]NEED to LOOKUP[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Process3[/TD]
[TD="align: center"]NEED to LOOKUP[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Process4[/TD]
[TD="align: center"]NEED to LOOKUP[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Process5[/TD]
[TD="align: center"]NEED to LOOKUP[/TD]
[/TR]
</tbody>[/TABLE]

So, in SheetA, the process are multiline. In SheetB I have separated them out, and now in Sheet B I want to look up the Master Name against the multiline cell in SheetA.

Hopefully, that's a bit clearer.

 

[AlanY]

may be you can try this

Book1
	A	B
1	Process1 Process2 Process3	Master1
2	Process4 Process5	Master2
3	Process5
4	Process6 Process7	Master3
5	Process8	Master4
A

Book1
	A	B
1	Process1	Master1
2	Process2	Master1
3	Process3	Master1
4	Process4	Master2
5	Process5	Master2
B

Cell Formulas
Range	Formula
B1	=INDEX(A!$B$1:$B$5,MATCH("*"&A1&"*",A!$A$1:$A$5,0))

 

[MrOaks]

may be you can try this

	A	B
Process1 Process2 Process3	Master1
Process4 Process5	Master2
Process5
Process6 Process7	Master3
Process8	Master4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

A​

	A	B
Process1
Process2
Process3
Process4
Process5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #E2EFDA"]Master1[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]Master1[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA"]Master1[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA"]Master2[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA"]Master2[/TD]

</tbody>

B​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=INDEX(A!$B$1:$B$5,MATCH("*"&A1&"*",A!$A$1:$A$5,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You're a hero, Alan.

Thank you.

 

[AlanY]

you're welcome