help! excel lookup!

amieee

New Member
Joined
Oct 19, 2006
Messages
4
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 =]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Welcome to the board.

Code:
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
ABCD
1STATECountysalesman
2AZJeffersonAlex
3AZPhoenixFrank
4NJBurlingtonBob
5NYQueensJohn
6TNWayneDan
7TNBlancoEd
8TXBlancoJay
Sheet1
Book4
ABCD
1STATECountysalesman
2AZJeffersonAlex
3NYJefferson#N/A
4NYQueensJohn
5TNBlancoEd
6TXBlancoJay
Sheet2
 

amieee

New Member
Joined
Oct 19, 2006
Messages
4
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

New Member
Joined
Oct 19, 2006
Messages
4
=\ for some reason its not working... i replaced "county" with a space but in the spreadsheet it still says #NUM!
 

amieee

New Member
Joined
Oct 19, 2006
Messages
4
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?
 

Forum statistics

Threads
1,136,519
Messages
5,676,333
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top