Look up value

[psycoperl]

I have a seating chart (Sheet1) where I have indicated where specific people will sit. In Sheet3 I have a list of the people. I would like to do a lookup in Sheet3 where it displays the seat assigned in Sheet1 for each conflictID. The seat ID is located two rows above the ID is entered.

So for example, in Sheet3 Column A it should display "EVE/B5150-CTR A-09"

SeatChartFA22EVE.xlsx
	K	L	M	N	O
69	9	10	11	12	13
70	EVE/B5150-CTR C-09	EVE/B5150-CTR C-10	EVE/B5150-CTR C-11	EVE/B5150-CTR C-12	EVE/B5150-CTR C-13
71				LAW
72				0093 LAW 1101 ETRC C-120
73
74	9	10	11	12	13
75	EVE/B5150-CTR B-09	EVE/B5150-CTR B-10	EVE/B5150-CTR B-11	EVE/B5150-CTR B-12	EVE/B5150-CTR B-13
76
77
78
79	9	10	11	12	13
80	EVE/B5150-CTR A-09	EVE/B5150-CTR A-10	EVE/B5150-CTR A-11	EVE/B5150-CTR A-12	EVE/B5150-CTR A-13
81	LAW			CMP
82	0010 LAW 1101 CTRB C-120			0169 CMP 2850 GMWA C-120
Sheet1

Cell Formulas
Range	Formula
K70:O70,K80:O80,K75:O75	K70	=CONCAT($C$1,"/", $E$1, "-", K$2, " ", MID($A70,6,1), "-", TEXT(K69,"00"))
K71:O71,K81:O81,K76:O76	K71	=MID(K72,6,3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B82:AJ82,B77:AJ77,B67:AJ67,B57:AJ57,B47:AJ47,B37:AJ37,B32:AJ32,B27:AJ27,B22:AJ22,B17:AJ17,B12:AJ12,B7:AJ7,B62:AJ62,B52:AJ52,B72:AJ72,B42:AJ42	Expression	=SUM(COUNTIFS($B$82:$AJ$82,B7),COUNTIFS($B$77:$AJ$77,B7), COUNTIFS($B$72:$AJ$72,B7), COUNTIFS($B$67:$AJ$67,B7), COUNTIFS($B$62:$AJ$62,B7), COUNTIFS($B$57:$AJ$57,B7), COUNTIFS($B$52:$AJ$52,B7), COUNTIFS($B$47:$AJ$47,B7), COUNTIFS($B$42:$AJ$42,B7), COUNTIFS($B$37:$AJ$37,B7), COUNTIFS($B$32:$AJ$32,B7), COUNTIFS($B$27:$AJ$27,B7), COUNTIFS($B$22:$AJ$22,B7), COUNTIFS($B$17:$AJ$17,B7), COUNTIFS($B$12:$AJ$12,B7), COUNTIFS($B$7:$AJ$7,B7))>1	text	NO
B82:AJ82,B77:AJ77,B67:AJ67,B57:AJ57,B47:AJ47,B37:AJ37,B32:AJ32,B27:AJ27,B22:AJ22,B17:AJ17,B12:AJ12,B7:AJ7,B62:AJ62,B52:AJ52,B72:AJ72,B42:AJ42	Expression	=SUM(COUNTIFS($B$82:$AJ$82,B7),COUNTIFS($B$77:$AJ$77,B7), COUNTIFS($B$72:$AJ$72,B7), COUNTIFS($B$67:$AJ$67,B7), COUNTIFS($B$62:$AJ$62,B7), COUNTIFS($B$57:$AJ$57,B7), COUNTIFS($B$52:$AJ$52,B7), COUNTIFS($B$47:$AJ$47,B7), COUNTIFS($B$42:$AJ$42,B7), COUNTIFS($B$37:$AJ$37,B7), COUNTIFS($B$32:$AJ$32,B7), COUNTIFS($B$27:$AJ$27,B7), COUNTIFS($B$22:$AJ$22,B7), COUNTIFS($B$17:$AJ$17,B7), COUNTIFS($B$12:$AJ$12,B7), COUNTIFS($B$7:$AJ$7,B7))=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K77:O77	List	=Sheet3!$B$2:$B$66
K82:O82	List	=Sheet3!$B$2:$B$66
K72:O72	List	=Sheet3!$B$2:$B$66

SeatChartFA22EVE.xlsx
	A	B	C
1	SEAT	DONE?	conflictID
2		1	0010 LAW 1101 CTRB C-120
3		1	0018 BUS 2000 STL B-90
4		1	0036 BUS 2000 STL B-90
5		1	0037 BUS 2000 STL B-90
6		1	0044 LAW 1101 PMWA C-120
7		1	0050 MKT 3000 DMWB -
8		1	0051 MKT 3000 DMWA A-60
9		1	0052 LAW 1101 QTRB C-120
10		1	0053 BUS 2000 STL B-90
11		1	0066 BUS 2000 STL B-90
12		1	0074 LAW 1101 PMWA C-120
Sheet3

Cell Formulas
Range	Formula
B2:B12	B2	=SUM(COUNTIFS(Sheet1!$B$82:$AJ$82,Sheet3!C2),COUNTIFS(Sheet1!$B$77:$AJ$77,Sheet3!C2), COUNTIFS(Sheet1!$B$72:$AJ$72,Sheet3!C2), COUNTIFS(Sheet1!$B$67:$AJ$67,Sheet3!C2), COUNTIFS(Sheet1!$B$62:$AJ$62,Sheet3!C2), COUNTIFS(Sheet1!$B$57:$AJ$57,Sheet3!C2), COUNTIFS(Sheet1!$B$52:$AJ$52,Sheet3!C2), COUNTIFS(Sheet1!$B$47:$AJ$47,Sheet3!C2), COUNTIFS(Sheet1!$B$42:$AJ$42,Sheet3!C2), COUNTIFS(Sheet1!$B$37:$AJ$37,Sheet3!C2), COUNTIFS(Sheet1!$B$32:$AJ$32,Sheet3!C2), COUNTIFS(Sheet1!$B$27:$AJ$27,Sheet3!C2), COUNTIFS(Sheet1!$B$22:$AJ$22,Sheet3!C2), COUNTIFS(Sheet1!$B$17:$AJ$17,Sheet3!C2), COUNTIFS(Sheet1!$B$12:$AJ$12,Sheet3!C2), COUNTIFS(Sheet1!$B$7:$AJ$7,Sheet3!C2))
C2:C12	C2	=CONCAT(TEXT(D2,"0000")," ",J2," ",K2," ",L2," ",Q2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
2:2500	Expression	=$B2=0	text	NO
2:2500	Expression	=$B2=1	text	NO

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	SEAT	DONE?	conflictID
2	EVE/B5150-CTR A-09	1	0010 LAW 1101 CTRB C-120
3		1	0018 BUS 2000 STL B-90
4		1	0036 BUS 2000 STL B-90
5		1	0037 BUS 2000 STL B-90
6		1	0044 LAW 1101 PMWA C-120
7		1	0050 MKT 3000 DMWB -
8		1	0051 MKT 3000 DMWA A-60
9		1	0052 LAW 1101 QTRB C-120
10		1	0053 BUS 2000 STL B-90
11		1	0066 BUS 2000 STL B-90
12		1	0074 LAW 1101 PMWA C-120
Data

Cell Formulas
Range	Formula
A2:A12	A2	=LET(x,WRAPROWS(TOCOL(CHOOSEROWS($K$70:$O$83,1,3,6,8,11,13),,1),2),FILTER(TAKE(x,,1),TAKE(x,,-1)=C2,""))

 

[psycoperl]

How about

Fluff.xlsm
	A	B	C
1	SEAT	DONE?	conflictID
2	EVE/B5150-CTR A-09	1	0010 LAW 1101 CTRB C-120
3		1	0018 BUS 2000 STL B-90
4		1	0036 BUS 2000 STL B-90
5		1	0037 BUS 2000 STL B-90
6		1	0044 LAW 1101 PMWA C-120
7		1	0050 MKT 3000 DMWB -
8		1	0051 MKT 3000 DMWA A-60
9		1	0052 LAW 1101 QTRB C-120
10		1	0053 BUS 2000 STL B-90
11		1	0066 BUS 2000 STL B-90
12		1	0074 LAW 1101 PMWA C-120
Data

Cell Formulas
Range	Formula
A2:A12	A2	=LET(x,WRAPROWS(TOCOL(CHOOSEROWS($K$70:$O$83,1,3,6,8,11,13),,1),2),FILTER(TAKE(x,,1),TAKE(x,,-1)=C2,""))

Could you please explain the formula that you are using. I have never heard of LET, WRAPROWS, Filter and Take. before.

 

[Fluff]

Does it work?

 

[psycoperl]

when I pasted it into my spreadsheet, no. But I am also thinking that I need to provide references to the other sheet where the source data is located. which is why I am trying to understand the formula's process.

 

[Fluff]

In what way doesn't it work?

 

[psycoperl]

No value returned

 

[Fluff]

Oops, forgot to add the sheet name, it should be

=LET(x,WRAPROWS(TOCOL(CHOOSEROWS(Sheet1!$K$70:$O$83,1,3,6,8,11,13),,1),2),FILTER(TAKE(x,,1),TAKE(x,,-1)=C2,""))