multiple conditions vlookup...

[engine22]

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

 

[brian.wethington]

I am sure there is a way to do this with a formula, and it is probably the better way to go but I provide some VBA to work something similar out here:

http://www.mrexcel.com/board2/viewtopic.php?t=282148&highlight=



This may not be helpful but you can be the judge of that. Have a good day.

 

[engine22]

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]

Brian's code should go in a standard module (ALT+F11, Insert>Module), not a worksheet module.

 

[engine22]

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]

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]

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]

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!

 

[engine22]

thx patsys i just saw ur reply...exactly what i needed