XLookup at Merged Cells

[nburaq]

Hi Gents,
Is there any solution to use vlookup or xlookup with merged cells? Here is the sample; I would like to find the correct group by entering the name in a cell. (I know merged cells always a problem for lookup functions but the file I am busy already prepared this way) Thanks in advance and good luck!

sample.xlsx
	A	B	C	D
1	Group	Name	Age	Formula
2	1	John	20
3		Alice	25
4		Bob	30
5	2	Sarah	45
6		Kim	40
7		Kevin	30
8	3	Tom	22
9		Sally	18
Go

 

[JamesCanale]

I couldn't think how to do it without a helper column. I'll be very interested to see someone can solve it more elegantly.

MrExcelPlayground4.xlsx
	A	B	C	D
1	Group	Name	Age	helper column
2	1	John	20	1
3		Alice	25	1
4		Bob	30	1
5	2	Sarah	45	2
6		Kim	40	2
7		Kevin	30	2
8	3	Tom	22	3
9		Sally	18	3
10
11	Lookup Person	Group
12	Kim	2
Sheet35

Cell Formulas
Range	Formula
D2:D9	D2	=MAX(A$2:A2)
B12	B12	=XLOOKUP(A12,B2:B9,D2:D9,"",0)

 

[Eric W]

I'm not so sure how elegant this is, but no helper column:

Book1
	A	B	C	D	E	F
1	Group	Name	Age	Formula	Name
2	1	John	20	1	Sally	3
3		Alice	25	1	Kim	2
4		Bob	30	1	Bob	1
5	2	Sarah	45	2	Kevin	2
6		Kim	40	2
7		Kevin	30	2
8	3	Tom	22	3
9		Sally	18	3
10
Sheet16

Cell Formulas
Range	Formula
F2:F5	F2	=LOOKUP(2,1/($A$2:INDEX($A$2:$A$100,MATCH(E2,$B$2:$B$100,0))<>""),$A$2:$A$100)
D2:D9	D2	=LOOKUP(2,1/($A$2:$A2<>""),$A$2:$A2)

 

[nburaq]

I couldn't think how to do it without a helper column. I'll be very interested to see someone can solve it more elegantly.

MrExcelPlayground4.xlsx
	A	B	C	D
1	Group	Name	Age	helper column
2	1	John	20	1
3		Alice	25	1
4		Bob	30	1
5	2	Sarah	45	2
6		Kim	40	2
7		Kevin	30	2
8	3	Tom	22	3
9		Sally	18	3
10
11	Lookup Person	Group
12	Kim	2
Sheet35

Cell Formulas
Range	Formula
D2:D9	D2	=MAX(A$2:A2)
B12	B12	=XLOOKUP(A12,B2:B9,D2:D9,"",0)

I had the same solution but I thought there might have been another way to do it. Thanks for the help

 

[nburaq]

I'm not so sure how elegant this is, but no helper column:

Book1
	A	B	C	D	E	F
1	Group	Name	Age	Formula	Name
2	1	John	20	1	Sally	3
3		Alice	25	1	Kim	2
4		Bob	30	1	Bob	1
5	2	Sarah	45	2	Kevin	2
6		Kim	40	2
7		Kevin	30	2
8	3	Tom	22	3
9		Sally	18	3
10
Sheet16

Cell Formulas
Range	Formula
F2:F5	F2	=LOOKUP(2,1/($A$2:INDEX($A$2:$A$100,MATCH(E2,$B$2:$B$100,0))<>""),$A$2:$A$100)
D2:D9	D2	=LOOKUP(2,1/($A$2:$A2<>""),$A$2:$A2)

Hmm things getting very interesting I love brainstorming

 

[Domenic]

This one is similar to @Eric W 's solution . . .

F2, copied down:

=LOOKUP(9.9999999999999E+307,$A$2:INDEX($A$2:$A$100,MATCH(E2,$B$2:$B$100,0)))

Or, if you don't like seeing that big number, first define the following name (Ribbon >> Formulas Tab >> Defined Names Group >> Define Name) . . .

Name:  BigNum

Refers to:  9.99999999999999E+307

Then try...

=LOOKUP(BigNum,$A$2:INDEX($A$2:$A$100,MATCH(E2,$B$2:$B$100,0)))