Two Variable Lookups
Tomkat is trying to cap mileage paid based on production. The mileage cap varies based on production and site. Read this newsgroup post for details if you like. This newsgroup post is where it all started.
This calls for a VLOOKUP/MATCH combo if I ever saw it.
The formula in E15 (and filled down column E) is
=MIN(D15,VLOOKUP(B15,$A$2:$E$11,MATCH(C15,$C$1:$E$1,FALSE)+2))
MATCH: The MATCH function returns the column that matches the site entered. It’s used as the col_index_num argument of VLOOKUP, that is, which column VLOOKUP retrieves. You have to add two to the result because the range of “Sites” starts two cell right of the vlookup range.
VLOOKUP: The left-most column of the mileage cap table is filled with the lowest number of the appropriate range of numbers. Column B shows the range of numbers, but it’s just there for informational purposes. Because there is no fourth argument in the VLOOKUP, it will find the largest number that is not greater than the number it’s looking for. If, as in row 15, we’re looking for 342, it will find 300 because that’s the largest number in the list that’s not greater than 342.
MIN: The whole thing is wrapped in a MIN because it’s a cap. If the person claims less than the cap, then that value should be used, not the cap.
I hope that helps Tomkat.
Steve Britton:
Dick,
What I often find myself having to do is lookup from a huge table values of three of more critera. And to do that the only way I know how is through SUMPRODUCT. Example:
=SUMPRODUCT((’MY06 Freq’!$D$8:$D$5028=Calcs!$B4)*(’MY06 Freq’!$C$8:$C$5028=Calcs!C$2)*(’MY06 Freq’!$G$8:$G$5028))
Which would give me array’s of 1 * 1 * the value in the column I want. What are you’re thoughts on this method. Is there a better way?
-Steve
21 April 2006, 10:30 amThomas E. Lyons:
Thank you for this. The Vlookup was the first one I tried as you can see by my post on 4/18/06 I was doing a couple of things wrong which make sense now. First I was trying to incorporate a less than rather than MIN as I was unfamiliar with MIN or at least did not have a good understanding of its principles. Second I did not think to break my table in smaller incriments than what I was actually using. I wish I hadn’t gotten side tracked with the whole CHOOSE thing and I may have gotten a little closer on my own. “OH WELL” I learned a few things in the process. Thanks again for your excellent help.
21 April 2006, 11:15 amMichael:
This is about the many Excel ways to skin a cat. When I have a need for entries like Site1, Site2, Site3, I tend to use a custom number format such as “Site”0. From there I would use a
CHOOSE(C15,3,4,5) in place of the MATCH()+2.
Which approach has the lower overhead for the spreadsheet?
…Michael
25 April 2006, 5:33 amBrett:
I don’t think you need the CHOOSE with Michael’s (reply#3) technique; just C15+2 for col_index_num.
25 April 2006, 6:23 amMichael:
Brett -
Even better!
…mrt
25 April 2006, 8:03 amGareth:
Always looking for shorter formulas for same job I came up with this variation on the OP:
=MIN(D15,VLOOKUP(B15,$A$2:$E$11,RIGHT(C15)+2))
Yes, it`s a bit of a kludge, but I think still valid. A handy use of the default behaviour of RIGHT in assuming a “1″ if a number is not supplied.
26 April 2006, 1:21 pmitsso:
Speaking about VLOOKUP…
VLOOKUP( lookup_value, table_array, col_index_num, range_lookup)
The help indicates that “If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value”
I noticed that this is not true when VLOOKUP_VALUE is a reference to a calculated value e.g. VLOOKUP (A3,… where cell A3 contains a formula e.g. =A1&A2.
When VLOOKUP_VALUE is a constant or a reference to a constant the function works as explained in the help.
Any ideas how to overcome this “feature”?
10x
1 June 2006, 5:04 pmitsso:
My mistake. Works. The array was not sorted…
1 June 2006, 5:56 pmbig gator:
i need to populate a spreadsheet based on looking at a horizontal string of variables and a vertical string of variables and retrieving the intersection of those two points for a very large worksheet of data. is there a vlookup hlookup combo that will work here? a two variable lookup.
26 March 2008, 6:04 ambig gator:
i’m using a data validation list with months and when a given month is chosen i want the summary page to populate the given data from one of the monthly worksheets? any ideas besides a huge if statement. i’d heard the choose formula is useful here.
26 March 2008, 6:06 amWalt:
Hi, I’m having a problem with vlookup, indexing and matching because I have to many variables due to constants and variables in three different columns. This is due to Cost Centres(CC), Dept, Nominal Coding(NC), to give budgeted amounts.see Table below. I want to lookup the amounts for each unique NM,CC,& Dept. The spreadsheet is much bigger than this, and I need to have the figures on another worksheet.
CN CC DEPT AMOUNT
1 April 2008, 4:18 am60611000 300 800 5498.00
60611000 360 920 2185.00
60612000 300 800 58401.00
60612000 360 920 24958.00
60614000 300 800 18625.00
60614000 360 920 8011.00
60620000 200 505 258.00
60620000 200 563 123.00
60620000 200 585 140.00
60620000 200 595 234.00
60620000 201 556 205.00
60620000 201 560 36.00
60620000 220 505 392.00
60620000 221 585 1055.00
60620000 223 560 917.00
60620000 300 520 8000.00
60620000 300 525 23.00
60620000 300 800 8643.00
60620000 320 900 12500.00
60631000 300 800 197.00
60631000 300 830 4200.00
60632000 300 800 5150.00
60632000 360 920 515.00
60640000 200 585 5000.00
60640000 201 560 167.00
60640000 220 505 2.00
60640000 223 560 0.00
60640000 285 750 0.00
60640000 300 520 74.00
60640000 300 800 7922.00
60683000 200 585 3313.00
60683000 201 560 196.00
60683000 202 560 786.00
Michael:
Hi Walt -
If I understand correctly, you want to separately sum all the CN entries, the CC entries, and the DEPT entries. Use SUMIF(). Assuming your data are in A:D, then for example in F2:
=SUMIF(A:A,A2,$D:$D)
and fill right. (=7683 =116733 =104436 for 60611000,300,800)
…mrt
1 April 2008, 8:11 am