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:

Process1 Process2 Process3	Master1
Process4 Process5	Master2

<tbody>
</tbody>

Example split out sheet:

Process1	??
Process2	??
Process3	??
Process4	??
Process5	??

<tbody>
</tbody>

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
1	Process1	Master1
2	Process2
3	Process3
4	Process4	Master2
5	Process5

<tbody>
</tbody>

SHEETA​

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

<tbody>
</tbody>

SHEETB​

Worksheet Formulas Cell 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))) <tbody> </tbody>

<tbody>
</tbody>

Ah, I see now. That makes sense.

However my data looks a bit different.

SheetA

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

<tbody>
</tbody>

SheetB

	A	B
1	Process1	NEED to LOOKUP
2	Process2	NEED to LOOKUP
3	Process3	NEED to LOOKUP
4	Process4	NEED to LOOKUP
5	Process5	NEED to LOOKUP

<tbody>
</tbody>

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
1	Process1 Process2 Process3	Master1
2	Process4 Process5	Master2
3	Process5
4	Process6 Process7	Master3
5	Process8	Master4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

A​

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

B​

Worksheet Formulas Cell Formula B1 =INDEX(A!$B$1:$B$5,MATCH("*"&A1&"*",A!$A$1:$A$5,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You're a hero, Alan.

Thank you.

 

[AlanY]

you're welcome