Match a person's name to multiple states

[mkyb14]

always feels good to come back to Mr. Excel, feels like I'm learning something new.

Anyways, I have a master sheet of accounts with a state they are located in.
On another sheet I have an engineer assigned and multiple states they will cover for those accounts.

Sheet 1
Company A, CA, insert name of engineer from sheet 2 here<insert engineer="" name="">
Company B, NY, same<insert engineer="" name="">

Sheet 2 - legend for assignments
John Smith, CA, TX, OK
Jane Oliver, TN, WA, NY
James Dunn, OH,MN,ILL,ND,SD,MT

How can I in sheet 1, reference the assignments and state of the company and return the name of the engineer? I've been bouncing around google and can't seem to make anything work. is this an index,match etc? since there are something like 9 engineers assigned to all the states </insert></insert>

 

[Joyner]

Hello, how is your data laid out? Is "Company A, CA" in one cell, or is "Company A" and "CA" in separate cells?

Same for this: John Smith, CA, TX, OK are each of the items, name and each state in separate cells?

 

[mkyb14]

Hello, how is your data laid out? Is "Company A, CA" in one cell, or is "Company A" and "CA" in separate cells?

Same for this: John Smith, CA, TX, OK are each of the items, name and each state in separate cells?

yes, everything is in a separate column.

 

[mabalo76]

you can use Index and sumproduct function to get what you want

=INDEX(sheet2!$A$1:$G$3,SUMPRODUCT(--(sheet2!$A$1:$G$3=B2)*ROW(sheet2!$A$1:$G$3)),1)

sheet2!$A$1:$G$3 is legend for assignments
B2 is the company location(state)

 

[mkyb14]

you can use Index and sumproduct function to get what you want

=INDEX(sheet2!$A$1:$G$3,SUMPRODUCT(--(sheet2!$A$1:$G$3=B2)*ROW(sheet2!$A$1:$G$3)),1)

sheet2!$A$1:$G$3 is legend for assignments
B2 is the company location(state)

Thought i did it correctly, but get an error.
https://docs.google.com/spreadsheets/d/101fhnyPKVW_cgkuK7xTycgx3SzQJoDf_B5FawZT196k/edit?usp=sharing

 

[mabalo76]

change the range to $A$1:$D$7, then it will work fine.

The first state "ca": sumproduct(XXXX) return 5 which is the row count from row1, but the range you chosen only have 3 rows. Errors then happened.

 

[Aladin Akyurek]

@ mkyb14

Book1
	A	B	C	D
4	Engineer Name	States they support
5	John Smith	ca	tx	ok
6	Jane Smith	az	tn	mn
7	James Dunn	wa	or	md
Sheet2

Book1
	A	B	C
1	Company Name	State	engineer match state
2	Company A	ca	John Smith
3	Company B	tx	John Smith
4	Company C	mn	Jane Smith
5	Company D	md	James Dunn
Sheet1

In C2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Sheet2!$B$5:$D$7,$B2),INDEX(Sheet2!$A$5:$A$7,MIN(IF(Sheet2!$B$5:$D$7=$B2,ROW(Sheet2!$A$5:$A$7)-ROW(INDEX(Sheet2!$A$5:$A$7,1,1))+1))),"nat available")

 

[mkyb14]

@ mkyb14

	A	B	C	D
Engineer Name	States they support
John Smith	ca	tx	ok
Jane Smith	az	tn	mn
James Dunn	wa	or	md

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet2​

	A	B	C
Company A	ca	John Smith
Company B	tx	John Smith
Company C	mn	Jane Smith
Company D	md	James Dunn

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Company Name[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]engineer match state[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

In C2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Sheet2!$B$5:$D$7,$B2),INDEX(Sheet2!$A$5:$A$7,MIN(IF(Sheet2!$B$5:$D$7=$B2,ROW(Sheet2!$A$5:$A$7)-ROW(INDEX(Sheet2!$A$5:$A$7,1,1))+1))),"nat available")

That worked, Thanks!

 

[Aladin Akyurek]

That worked, Thanks!

You are welcome.