multiple conditions vlookup...

engine22

Board Regular
Joined
Jul 5, 2007
Messages
62
I want to create a vlookup formula that will look for a value in column A, then another value in column B to return the value in column C. Here is a simplify version of my table:

A B C
1 urban local 10
2 urban collector 20
3 rural local 30
4 rural collector 40


let say i want to find the value when my road 1 is a rural collector...

Inventory:

X Y Z
1 name environtment roadtype
2 1 rural collector
...

how could i create a formula to do so... without using "if" because my table is much larger than this one...

maybe an index/match combo could work???

thank you in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

engine22

Board Regular
Joined
Jul 5, 2007
Messages
62
IO have been trying to use your code but i always get a name error... maybe im not recording the vba appropriately... i right click on my tab...view code...then i paste your code in...after????
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Brian's code should go in a standard module (ALT+F11, Insert>Module), not a worksheet module.
 

engine22

Board Regular
Joined
Jul 5, 2007
Messages
62
thanks noirie I am real close to i now...it works in the workbook where i am using the data but it doesnt work it the workbook i wanna use the formula
 

engine22

Board Regular
Joined
Jul 5, 2007
Messages
62
ok i got it to work!!! i had to modules lookup2 in one workbook... i just had to delete the first one a created in one workbook...thanks to both of you its appreciated... nice function brian
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
I want to create a vlookup formula that will look for a value in column A, then another value in column B to return the value in column C. Here is a simplify version of my table:

A B C
1 urban local 10
2 urban collector 20
3 rural local 30
4 rural collector 40


let say i want to find the value when my road 1 is a rural collector...

Inventory:

X Y Z
1 name environtment roadtype
2 1 rural collector
...

how could i create a formula to do so... without using "if" because my table is much larger than this one...

maybe an index/match combo could work???

thank you in advance

Assuming your criteria are in Y2 (="rural") and Z2 (="collector")

=INDEX($C$1:$C$4,MATCH(1,INDEX((A1:A4=Y2)*(B1:B4=Z2),0),0))
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Man I new there was a way, I just never have been able to the the formula to work the route I was taking... Thank you Patsys!
 

Forum statistics

Threads
1,181,365
Messages
5,929,548
Members
436,677
Latest member
CathalP1992

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