help! excel lookup!

amieee

New Member
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:
STATE County salesman
AZ Jefferson Alex
AZ Phoenix Frank
NJ Burlington Bob
NY Queens John
TN Wayne Dan
TN Blanco Ed
TX Blanco Jay

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
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
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
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
=\ for some reason its not working... i replaced "county" with a space but in the spreadsheet it still says #NUM!

amieee

New Member
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?

Replies
6
Views
444
Replies
3
Views
364
Replies
8
Views
354
Replies
5
Views
150
Replies
9
Views
338

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.

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

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