Look up a value based on multiple criteria, including approximate date Hello,, I
ID: 3569244 • Letter: L
Question
Look up a value based on multiple criteria, including approximate date
Hello,,
I am looking to validate factors in one table (Validation Table) against factors in a Control Table. These factors are determined by company, state and date. The factors returned in the validation table should be those whose dates come close to, without going over, or the same as in the control table. For example, the factor returned from the control table for Company A, Arkansas, with a date of 1/31/2009 (date of 5/31/2009 in control table) should be 1.5. The factor for company B, Maine, with a date of 7/15/2011 should be 2.2. As there are thousands of lines of data, I want to avoid using named ranges in the formula.....
Thank you,!!
Chris
4.7
Validation Table Control Table Company State Date Factor Factor from control table Company State Date Factor A AK 1/31/2009 1.5 1.5 A AK 5/31/2009 1.5 A AK 2/28/2010 2 2 A AK 3/31/2010 2 A AK 7/15/2011 3.5 3.5 A AK 7/31/2011 3.5 A LA 2/16/2009 1.6 1.6 A LA 2/28/2009 1.6 A LA 6/30/2012 1.9 1.9 A LA 8/31/2012 1.9 A LA 8/8/2013 1.7 1.7 A LA 1/1/2014 1.7 A TX 3/1/2010 4.3 4.4 A TX 4/30/2011 4.4 A TX 4/5/2011 4.4 4.4 A TX 6/17/2012 4.5 A TX 6/17/2012 4.5 4.5 B ME 8/31/2009 1.7 B ME 1/31/2009 1.7 1.7 B ME 8/15/2011 2.2 B ME 2/28/2010 2.2 2.2 B MO 2/28/2009 1.8 B ME 7/15/2011 3.7 2.2 B MO 7/31/2012 2.1 B MO 2/16/2009 1.8 1.8 B MO 8/8/2013 1.9 B MO 6/30/2012 2.1 2.1 B NM 3/1/2010 4.6 B MO 8/31/2012 1.9 1.9 B NM 4/5/2011 4.6 B NM 3/1/2010 4.5 4.6 B NM 6/17/2012 4.7 B NM 4/5/2011 4.6 4.6 B NM 6/17/2012 4.74.7
Explanation / Answer
Hi,,,
Try this array formula::
=INDEX($J$3:$J$18,MATCH(C3,IF(($G$3:$G$18=A3)*($H$3:$H$18=B3),$I$3:$I$18),-1)).
To enter with CTRL+SHIFT+ENTER
The control table must be sorted:
- Company: A to Z
- State: A to Z
- Date: Newest to oldestt
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.