vlookup for more than 2 lookup values

[nolagoatgirl14]

I am trying to do a vlookup based on 4 values.

I have 4 drop down data validations in a row. The first one has the type of material, the second has the height, the third has the width, and the forth a depth. My data is set up in this manner because it's a lot of data and trying to select with more specific names is very long, there are over 700 data combinations. Some data are words and other are numbers.

What I want is for the vlookup to look at all 4 cells and bring back the area that fits all 4 requirements.

If vlookup is not the way to go, that's fine I'm open to suggestions. I've also tied concatenate and A1&A2etc and A1&" "&A2 etc. I get either #N/A or #VALUE in return.

Any suggestions or resources would be greatly appreciated.

Thanks.

 

[hiker95]

nolagoatgirl14,

Welcome to the MrExcel forum.

See:

Index Match - with 4 criteria
http://www.mrexcel.com/forum/showthread.php?t=467576


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[nolagoatgirl14]

http://www.box.net/files#/files/0/f/0/1/f_722038644 (You'll have to download it)

In the tab "Steel" in row 2, I copied what I'm trying to do in tab "Interface". Seemed easier to have it all on one tab for now. I had to include all tabs because everything is so interlinked together.

F2 is a concatenate formula to combine the 4 criteria for searching. G7 is using & as a function to achieve the same thing. I would just focus on "Mechanical Tube" and adjust C2, D2, and E2 for these purposes for now.

A2:E2 are the drop down menus for selection of the materials contained on this page.

The data is a named range, "SteelData" and row 1 is just the column number.

I want the formula to use A2, C2, D2, and E2 to find a match in Column M (13) for the area which will be used to test the material against the needed area for structural purposes. If any one of these drop down menu selections changes, then the area will also update.

Thanks.

 

[Akashwani]

Hi,

Welcome to MrExcel.

See if this helps, it's probably not going to solve your problem, but it may help point you in the right direction.

http://www.youtube.com/user/bjele123#p/search/41/0FB4yGcCk_Q

Good luck.

Ak

 

[hiker95]

nolagoatgirl14,

Your link to Box.Net is asking for an ID and Password.

Please try again to send us a link to your workbook.

 

[nolagoatgirl14]

http://www.box.net/shared/cpnka0qjj3

Sorry about that.

I tried some of the INDEX Match but it only brought back the last value for Mechanical Tube, completely ignoring the other three criteria.
=INDEX(Steel!M7:M2000,MATCH(1,IF(Steel!B7:B2000=Interface!B41,IF(Steel!D7:D2000=Interface!D41,IF(Steel!E7:E2000=Interface!E41,IF(Steel!I7:I2000=Interface!F41,1)),0))))
And I did do ctr shift enter

I'll have to watch that youtube video again, a lot happens to quickly.

Thanks

 

[nolagoatgirl14]

I fixed it. The Index Match function worked. The trouble boiled down to having text and numbers in the same column for matching. Once I converted everything to decimal, it worked just fine.

I'm still curious about other solutions to this challenge though. I have a lot more work to do on this workbook.

Thanks.