Lookup Value based upon criteria

[smalik]

I regularly pull data from a system that has 14 fields for different roles. Some of these fields are empty and some have values. The value is the name of the employee (if that helps)

The fourteen fields are:

• Field-1
• Field-2
• Field-3
• etc.

I need to pull data for four roles only. The four roles are:

• Sales
• Service
• Contact
• Admin

The issue I am facing is that these four roles are never in the same location from office to office. For example, "Sales" employees may be listed in "Field-1" for Office-1 but for Office-2, "Sales" employees may be listed in "Field-8" and same is true to other three fields.

I thought defining these fields than writing a nested "if statement" with fourteen "hlookups" statements. However, this is very cumbersome and prone to errors. Is there a better way to write a formula if I define somewhere else which field is "Sales" vs. "Contact" vs. "Admin"?

I am sorry, I can only show a picture as my work computer does not allow me to download any add-ons.

Here is what I have:
The data from the source system is in "Columns G" through "Column T"
I need to pull the required fields in "Column B" through "Column E"
I created a "Table Definition" in a separate tab where I identify the required field

I hope I explained it well, but please feel free to ask for more clarity, if need be.

Thanks for your help in advance.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Sales Lead	Service Lead				Field-1	Field-2	Field-3	Field-4	Field-5	Field-6	Field-7	Field-8	Field-9	Field-10	Field-11	Field-12	Field-13	Field-14
3		a	d				a		d
4		b	e				b		e
5		c					c
6
7
8
9
10				Field-1	Sales Lead
11				Field-2
12				Field-3	Service Lead
13				Field-4
14				Field-5
15				Field-6
16				Field-7
17				Field-8
18				Field-9
19				Field-10
20				Field-11
21				Field-12
22				Field-13
23				Field-14
Main

Cell Formulas
Range	Formula
B3:B5,C3:C4	B3	=LET(f,FILTER($G$3:$T$12,$G$2:$T$2=FILTER($D$10:$D$23,$E$10:$E$23=B2)),FILTER(f,f<>""))
Dynamic array formulas.

 

[smalik]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Sales Lead	Service Lead				Field-1	Field-2	Field-3	Field-4	Field-5	Field-6	Field-7	Field-8	Field-9	Field-10	Field-11	Field-12	Field-13	Field-14
3		a	d				a		d
4		b	e				b		e
5		c					c
6
7
8
9
10				Field-1	Sales Lead
11				Field-2
12				Field-3	Service Lead
13				Field-4
14				Field-5
15				Field-6
16				Field-7
17				Field-8
18				Field-9
19				Field-10
20				Field-11
21				Field-12
22				Field-13
23				Field-14
Main

Cell Formulas
Range	Formula
B3:B5,C3:C4	B3	=LET(f,FILTER($G$3:$T$12,$G$2:$T$2=FILTER($D$10:$D$23,$E$10:$E$23=B2)),FILTER(f,f<>""))
Dynamic array formulas.

Thank you... This formula works but I get into an issue:

Shame on me for not giving the full picture.
Each row is a record. The Sales Lead, Service Lead, etc., are all related to that records. Therefore, if there is no name in the filed, the desired result should be blank or whatever is in the source column.

For example, Sales-3 is for record 6 in the above example, so records 3,4,&5 should be blank in Column B. Then "Sales-3" should come for record 6.

In addition, the formula auto fills itself. When I try to copy the formula down from b3 down, I get the "#spill!" error. Any idea how to fix this?

 

[Fluff]

When I try to copy the formula down from b3 down

Don't, just put it in B3 & drag across.
To keep the blanks use

=LET(f,FILTER($G$3:$T$12,$G$2:$T$2=FILTER($D$10:$D$23,$E$10:$E$23=B2)),IF(f="","",f))

 

[smalik]

Don't, just put it in B3 & drag across.
To keep the blanks use

=LET(f,FILTER($G$3:$T$12,$G$2:$T$2=FILTER($D$10:$D$23,$E$10:$E$23=B2)),IF(f="","",f))

Thank you.... this worked.

Sorry for the late reply.

 

[Fluff]

You're welcome & thanks for the feedback.