Conditional VLOOKUPS

[Tom01]

Tried posting this earlier but I can't find it so I will try again. I need to find the result from a VLOOKUP table(s) but which table to use will be dependent upon a series of conditions (over 200 different possible results of the input conditions). I was thinking that I could possibly store each of the 200+ separate VLOOKUP operations in a different cell in the spreadsheet and then try to execute the VLOOKUP for that cell based upon the result of the conditions test. I will provide a simple example below but my actual needs will be more complex than this. Hoping I will be able to add the extra layers of complexity as long as someone can help me get started.
Example - Ordering a shirt
Color options: Red, Green, Blue, Yellow
Neck Size options: 14, 15, 16, 17, 18
Pattern options: Solid, Plaid, Stripes
Sleeve options: Short, Long
Type: Oxford, Button Down

So if the user inputs a Green, 16 inch, Plaid, Long Sleeve, Button Down shirt, I would need to perform a VLOOKUP based upon this specific condition. A Blue, 15 inch, Solid, Short Sleeve, Oxford shirt would require a completely different VLOOKUP from a completely separate table.

I have never created a VBA or macro but it seems as if I need to learn???

 

[dms37]

In my experience the easiest way to do things like this is a bit of a cheat. Assuming you have all possible combinations listed in the table create a helper column which combines them as one string - something like:

Green|16 inch|Plaid|Long Sleeve|Button Down

That can be done using =A2&"|"&B2&"|"&C2...... and so on as often as is needed then copied down

Then just do the vlookup to that something like (assume all of that is in a sheet called Data and in that Data tab the helper column is in H and the thing you're looking up is in I for no particular reason):

=VLOOKUP(A2&"|"&B2&"|"C2&"|"D2&"|"&E2,Data!$H$2:$I$5000,2,0)

If you need me to expand on this "off the top of my head" approach just say so.

 

[Tom01]

Thanks for the response dms37 but my problem is further complicated by the fact that I have to use a separate table for each of these VLOOKUPS. My example of ordering a shirt is really much more simple than my real life issue and to nest the over 200 different possible combinations would be overly complex and would make debugging or expanding in the future a major pain.

 

[Tom01]

dms37,

Using your approach I may be able to get very close to what I need. The problem I am having with the concatenation into a string is that one of the values needs to be numeric instead of alpha. It is fine for the previous concatenated items to be alpha or labels but the last qualifier is truly a length range and when I concatenate it along with the previous items it reads as a label so 102 inches reads as being smaller than 78 inches. Any quick way to make the last element in the concatenated key a numeric value? I think I can come up with another workaround if this won't work but it will be a bit more time consuming.

Thanks in advance.

 

[drewg]

dms37,

Using your approach I may be able to get very close to what I need. The problem I am having with the concatenation into a string is that one of the values needs to be numeric instead of alpha. It is fine for the previous concatenated items to be alpha or labels but the last qualifier is truly a length range and when I concatenate it along with the previous items it reads as a label so 102 inches reads as being smaller than 78 inches. Any quick way to make the last element in the concatenated key a numeric value? I think I can come up with another workaround if this won't work but it will be a bit more time consuming.

Thanks in advance.

would wrapping the last element in the concatenation in TEXT(A1,"000") get you to stuff zero in front of 78 so it would sort correctly?
hth

 

[Tom01]

Genius !!! I think that will work !!!

Thanks all.

 

[drewg]

Genius !!! I think that will work !!!

Thanks all.

hardly...more like a stopped clock is right twice a day, but very happy and hope it works.

 

[Tom01]

Almost there but another question... The way that our tables are built in my previous discussion, my lookup value in the VLOOKUP command needs to match on the next highest match and not the row that contains the immediately preceding value when TRUE is selected for the "Range Lookup" function. Example - if my lookup value (which is calculated based upon a number of criteria) is 120 and I use this to lookup in a table that has values of

95
109
115
125
140

I need to return a factor from the table row that is on the 125 line and not the 115 line. Is there a way I can use the ROW ()+1 command within the VLOOKUP command itself to return the value from the next row? Or is there another way?

Thanks

 

[drewg]

Almost there but another question... The way that our tables are built in my previous discussion, my lookup value in the VLOOKUP command needs to match on the next highest match and not the row that contains the immediately preceding value when TRUE is selected for the "Range Lookup" function. Example - if my lookup value (which is calculated based upon a number of criteria) is 120 and I use this to lookup in a table that has values of

95
109
115
125
140

I need to return a factor from the table row that is on the 125 line and not the 115 line. Is there a way I can use the ROW ()+1 command within the VLOOKUP command itself to return the value from the next row? Or is there another way?

Thanks

First thing I would play with is the last argument in VLOOKUP, but you only get TRUE or FALSE there for exact. So given that, I'd investigate re-designing the task performed by VLOOKUP into some version of INDEX and MATCH because MATCH gives you a little more wiggle room in the rigor applied to the criteria as -1, 0, 1 for Less Than, Exact or Greater Than. So you'll need to restate your lookup along the lines of
(I work on the row first, so say =MATCH(ExampleOfTheThingToFind, TheSingleColumnToFinditIn, GiveMeLessThanExactOrGreaterNeighbor)
Once that piece is giving you the correct row (dont forget to make your addresses relative for the first and anchored for the second arg) you will plug this MATCH expression into
=INDEX(TheTableToFindItIn,MATCH(TheMishegasYouFiguredOutAbove),TheColumnToReturnIfFound)
I think if you structure your job this way you will have more control over the matching process and be able to get what you need.
hth
drew