Need help pulling information from two sheets into a third sheet

[ExcelinHR]

Hello everyone,

I thought i could do this, but its Monday, and its 6pm, and my brain is fried. :/

This is the scenario:

I have information in one sheet which shows locations where my company has stores.

I have another sheet with thousands of rows of information, but i only need the rows which have the same identifiers as the first sheet.

Sheet 1: (made up numbers/cities)

Location	Location City	Location State
841	Phoenix	AZ
842	Tucson	AZ
641	San Diego	CA
809	Sacramento	CA
814	Fresno	CA
822	Bakersfield	CA

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sheet 2: (has many columns, but i shortened it to 2)

Loctn	Hrly Rate
822	25.090000
826	14.000000
826	20.752500
826	21.122500

<colgroup><col><col></colgroup><tbody>
</tbody>

Etc. etc.


From this example, we can see that 822 on sheet 2 matches one of the codes on sheet 1 "Bakersfield".

I need sheet 3 to only contain rows from sheet 2 with location codes that match sheet 1.

Any help with this would be amazing. I really don't want to do it by hand :|

Thanks

 

[Danerida]

On Sheet 3 in Cell A2 (assuming A1 is your "Location" header):
=IFERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)),"")
This is an array formula and must be confirmed with CONTROL+SHIFT+ENTER or it won't work.
Copy down until you get blanks.
This formula will make a list of unique values from your data in Sheet 2. Adjust the ranges in the formula to reference your entire list of locations on sheet 2.

Dan

 

[ExcelinHR]

Hey Dan,

Thanks for your response!
Unfortunately i think i may have miscommunicated what i need for a final result.

What i need on the last sheet is a list of all the items on sheet 2 that occur in sheet 1.
Basically i need sheet 1 to act as a filter for sheet 2 to output on sheet 3.

I would just filter the results, but there are hundreds of these numbers i would have to go through

 

[Danerida]

Ok. Change it to this:
=IFERROR(IF(COUNTIF(Sheet1!$A$2:$A$100,INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)))>0,INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)),""),"")
This is an array formula and must be confirmed with CONTROL+SHIFT+ENTER or it won't work.

You'll need to adjust:COUNTIF(Sheet1!$A$2:$A$100

to match your data

Dan