help! excel lookup!

[amieee]

hey guys help me out here please

i have a list with salesman, state and county
another list with salesman, state and county except on this list the salesman is empty.

i need to find how to match the state with state and county with county to find the right saleman. the problem is some county names are the same so i need to match BOTH to get the right salesman. how do i do that?

heres an example:
spreadsheet1
STATE County salesman
AZ Jefferson Alex
AZ Phoenix Frank
NJ Burlington Bob
NY Queens John
TN Wayne Dan
TN Blanco Ed
TX Blanco Jay

spreadsheet2
STATE County salesman
AZ Jefferson
NY Jefferson
NY Queens
TN Blanco
TX Blanco


Thanx for any assistance you guys give =]

 

[Oaktree]

Welcome to the board.

Sheet2!C2 =INDEX(Sheet1!$C$2:$C$8,MATCH(1,--((Sheet1!$A$2:$A$8=Sheet2!A2)*(Sheet1!$B$2:$B$8=Sheet2!B2)),0))

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

Then, copy C2 down.

Book4
	A	B	C	D
1	STATE	County	salesman
2	AZ	Jefferson	Alex
3	AZ	Phoenix	Frank
4	NJ	Burlington	Bob
5	NY	Queens	John
6	TN	Wayne	Dan
7	TN	Blanco	Ed
8	TX	Blanco	Jay
Sheet1

Book4
	A	B	C	D
1	STATE	County	salesman
2	AZ	Jefferson	Alex
3	NY	Jefferson	#N/A
4	NY	Queens	John
5	TN	Blanco	Ed
6	TX	Blanco	Jay
Sheet2

 

[amieee]

THANKS SO MUCH! I know it works but now i have to change everything that i have bc one spreadsheet says

COUNTY
JEFFERSON

and the other one says

COUNTY
JEFFERSON COUNTY

any suggestions?

btw your the best! =) saves me alot of trouble

 

[Oaktree]

Is it just the word "county" at the end that is the difference? Or are there things like COOK in one and COOK CTY in the other?

If it's just the word "COUNTY", you can just select the cells and use edit --> replace to replace " COUNTY" (without the quotes, but with the space) with nothing to edit those out. If it's not that clean (as in the COOK CTY case), it's not that easy....

 

[amieee]

=\ for some reason its not working... i replaced "county" with a space but in the spreadsheet it still says #NUM!

 

[amieee]

oh yea and in sheet 2 i also have a column called ZIP where i had to use another spreadsheet that had a buncha zips and counties... and i got the county column from using LOOKUP code... does that affect this code?