Forumula to lookup zip codes assigned to a user and return their name

[arace1118]

Hello,

This one has stumped me for a few days now and I desperately need help. I thought simple lookup formulas would do the trick, but so far I can only get the desired result by having a ton of IFS functions (which is not efficient).

What I need to do is have a formula in "C" that is looking at "A", and return the name found in "B" that corresponds with the zip code owners name.
(This is a sample of a sheet that is 5,000+ records and 25+ users.)

Thank you in advance!!

 

[VBA Geek]

it's possible to do this look-up but why not arrange your table in M and N as a normal table with zip codes in one column and names in another so then it's a very simple vlookup?

 

[arace1118]

I could do that, but it would be very hard for my territory managers to keep up with data placed like that. I will do it that way if needed, but I'm hoping there is a better solution that would allow us to have the names at the top and zip codes below each person.

Thank you!

 

[VBA Geek]

You can use this formula. But do consider the option to rearrange the Names and Zip Codes in order to have a proper table, you can use faster formulas, pivot tables etc. And in general it's just how data should be arranged to make things easier

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			zip	city	state	Address 2	Notes	Status	Owner				Austin	Kenwin
2			75002	Portland	Or	rfjerfi	fqd	new owner	Austin				75098	75001
3													75002	75150
4													75999
5
6
7
8
Sheet1

Cell Formulas
Range	Formula
I2	{=INDEX($M$1:$N$1,MAX(IF(C2=$M$2:$N$5,COLUMN($M$1:$N$1)-COLUMN($M$1)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[arace1118]

That worked perfectly. Thank you for the help!