SUMIf Alternative for returnig Text from cell range

[toddlxa]

Good Morning,

I am looking for some help to replace a common formula I use to reference numerical values in providing some simple reference calculators/target guides for employees. I usually use the below to reference data I keep on a table in a worksheet called Data


=SUMIF(Data!A:A, J2, Data!B:B)

I need to build something similar, but will return text values when a employee enters a zipcode into a specific cell. I want it to pull up the area/managers/ect from my Data worksheet. The table I will be pulling from will look something like this, and have around 50k rows. Region, City, Manager, State will have many duplicates, but each zip value will be unique

Data Table

A(Region) B(ORG) C(Manager) D(City) E(State) F(Zip)
Region 1 E42 Carson, Sue Winter Springs FL 32708
Region 1 E43 Carson, Sue Oviedo FL 32765
Region 3 EB7 Smith, Bob Joliet IL 60435


I want my Calculator/Reference Guide to be on a seperate worksheet and look like this

A(ZIP) B(Region) C(ORG) D(Manager) E(City) F(State)

A will be the Cell for employees to enter a zip manually

Once entered I want the formulas in B, C, D, E, F to return the info from the corresponding zip row on the data sheet.

Any help would be greatly appreciated!

Thanks!

 

[steve the fish]

Hi. Something along the lines of this will work. You need to change the ranges to suit your data with sheet names etc. Also the headers must be identical in both worksheets.

=INDEX($A$1:$F$100000,MATCH(F2,$F$1:$F$100000,0),MATCH(A1,$A$1:$F$1,0))

 

[toddlxa]

Hi. Something along the lines of this will work. You need to change the ranges to suit your data with sheet names etc. Also the headers must be identical in both worksheets.

=INDEX($A$1:$F$100000,MATCH(F2,$F$1:$F$100000,0),MATCH(A1,$A$1:$F$1,0))

Thanks! That got me on the right track, I also wasn't thinking of needing an ' in front of the zip since i had the cells formatted. I ended up doing:

=Index(Data!B:B, Match($A$2,Data!A:A,0))

It pulls everything I need.