Lookup on Merged Cells and Multiple Columns Below

[Saacko]

Hey everyone, I have struggling with this for days now...and my team is depending on me to figure this out soon.

I need help with doing some sort of lookup on data that is in different columns below a merged header cell. I need a specific cell to be chosen based on the name that I input. For example, I will want to see what the Cost Type on the freight cost row is for Mark just by entering in his name on a separate sheet. Or another example, I want to know what the Plug % is for Luke just by entering his name in the other sheet.

Here is my data all cleaned up for your convenience:

Here is my sheet that shows how I will need the data to be summarized at:

Please let me know if you can help me, I would be very appreciative of any advice you can give.

 

[Drrellik]

Welcome to the board,

please try to upload a portion of your work using the XL2BB add on listed below it will help oters help you without have to try and recreate your work from scratch

DR

 

[Eric W]

Maybe:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Player 1			Player 2			Player 3
2		Key			Units	Mark			Luke			Paul
3	1 - Freight Cost	Unit Price	Cost Type	Total Cost per item	1	1	INCL	1	2	COST	2	3	PLUG	3
4
5						Plug $	Plug %	Total Price	Plug $	Plug %	Total Price	Plug $	Plug %	Total Price
6		Plug $	Plug %	Total Price per player		1.1	1.2	1.3	2.1	2.2	2.3	3.1	3.2	3.3
7
8
9
10	Player	Cost Type	Total Cost per item	Plug $	Plug %	Total Price per player
11	Mark	INCL	1	1.1	1.2	1.3
12	Luke	COST	2	2.1	2.2	2.3
13	Paul	PLUG	3	3.1	3.2	3.3
Sheet3

Cell Formulas
Range	Formula
B11:F13	B11	=LET(t,OFFSET($F$2,1,MATCH($A11,$F$2:$L$2,0)-1,4,3),v,CONCAT(IF(B$10=$B$3:$D$6,t,"")),IFERROR(--v,v))

 

[Saacko]

Help with Lookup Sheet.xlsx
	A	B	C	D		F		H	I	J	K	L	M	N	O	P
1		Project No.:	Project No.:	Project No.:				Player 1			Player 2			Player 3
2		Project Name:	Key			Units		Mark			Luke			Paul
3	1 - Freight Cost	Unit Price	Cost Type	Total Cost per item		1		$1.00	INCL	$ 1	$2.00	COST	$ 2	$3.00	PLUG	$ 3
4								Plug $	Plug %	Total Price	Plug $	Plug %	Total Price	Plug $	Plug %	Total Price
5	Freight	Plug $	Plug %	Total Price per player				$-		$-	$-		$-	$-		$-
6
7	Player	Cost Type	Total Cost for item	Plug $		Total Price per player
8	Mark
9	Luke
10	Paul
Player Info

 

[Saacko]

Help with Lookup Sheet.xlsx
	A	B	C	D		F		H	I	J	K	L	M	N	O	P
1		Project No.:	Project No.:	Project No.:				Player 1			Player 2			Player 3
2		Project Name:	Key			Units		Mark			Luke			Paul
3	1 - Freight Cost	Unit Price	Cost Type	Total Cost per item		1		$1.00	INCL	$ 1	$2.00	COST	$ 2	$3.00	PLUG	$ 3
4								Plug $	Plug %	Total Price	Plug $	Plug %	Total Price	Plug $	Plug %	Total Price
5	Freight	Plug $	Plug %	Total Price per player				$-		$-	$-		$-	$-		$-
6
7	Player	Cost Type	Total Cost for item	Plug $		Total Price per player
8	Mark
9	Luke
10	Paul
Player Info

Here is the Excel Sheet my apologies I'm new here. I did have one additional thing to say: the 3 columns with Mark, Luke, Paul etc. need to be able to be cut and re-inserted in different orders and still not mess up the formula. Thanks everyone

 

[Saacko]

Maybe:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Player 1			Player 2			Player 3
2		Key			Units	Mark			Luke			Paul
3	1 - Freight Cost	Unit Price	Cost Type	Total Cost per item	1	1	INCL	1	2	COST	2	3	PLUG	3
4
5						Plug $	Plug %	Total Price	Plug $	Plug %	Total Price	Plug $	Plug %	Total Price
6		Plug $	Plug %	Total Price per player		1.1	1.2	1.3	2.1	2.2	2.3	3.1	3.2	3.3
7
8
9
10	Player	Cost Type	Total Cost per item	Plug $	Plug %	Total Price per player
11	Mark	INCL	1	1.1	1.2	1.3
12	Luke	COST	2	2.1	2.2	2.3
13	Paul	PLUG	3	3.1	3.2	3.3
Sheet3

Cell Formulas
Range	Formula
B11:F13	B11	=LET(t,OFFSET($F$2,1,MATCH($A11,$F$2:$L$2,0)-1,4,3),v,CONCAT(IF(B$10=$B$3:$D$6,t,"")),IFERROR(--v,v))

Hey Eric, thank you so much for trying to help me on this I really appreciate it. I had one request, otherwise I think this works great. I was wondering: Will these formulas still work if I cut and re-insert the 3 columns that comprise Mark, Luke, Paul etc? Say I wanted to cut the 3 columns of Paul and put them in front of Mark, would the formulas still work? I tried and got an error so I am wondering if you had a work around. Again, thank you for your kind help

 

[Saacko]

Welcome to the board,

please try to upload a portion of your work using the XL2BB add on listed below it will help oters help you without have to try and recreate your work from scratch

DR

Drrellik,

Thank you for letting me know, sorry I am new here. I got XL2BB downloaded, and you can find my data sheet below.

 

[Eric W]

Hey Eric, thank you so much for trying to help me on this I really appreciate it. I had one request, otherwise I think this works great. I was wondering: Will these formulas still work if I cut and re-insert the 3 columns that comprise Mark, Luke, Paul etc? Say I wanted to cut the 3 columns of Paul and put them in front of Mark, would the formulas still work? I tried and got an error so I am wondering if you had a work around. Again, thank you for your kind help

Try:

=LET(t,OFFSET($A$2,1,MATCH($A11,$2:$2,0)-1,4,3),v,CONCAT(IF(B$10=$B$3:$D$6,t,"")),IFERROR(--v,v))

 

[Saacko]

Try:

=LET(t,OFFSET($A$2,1,MATCH($A11,$2:$2,0)-1,4,3),v,CONCAT(IF(B$10=$B$3:$D$6,t,"")),IFERROR(--v,v))

Hi Eric, sorry for the late response, I've been playing around with this and it is working now for me on my actual data. Thank you for your help on this.