| IndexMatchProblem.xlsx | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | |||
| 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | Time | 0600_1430_ | 0700_1300_ | 0700_1500_ | 0700_1530_ | 0800_1400_ | 0800_1430_ | 0800_1500_ | 0800_1530_ | 0800_1600_ | 0800_1630_ | 0900_1500_ | 0900_1530_ | 0900_1600_ | 0900_1630_ | 0900_1700_ | 0900_1730_ | 1000_1600_ | 1000_1630_ | 1000_1700_ | 1000_1730_ | 1000_1800_ | 1000_1830_ | 1100_1700_ | 1100_1730_ | 1100_1800_ | 1100_1830_ | 1100_1900_ | 1100_1930_ | 1200_1800_ | 1200_1830_ | 1200_1900_ | 1200_1930_ | 1200_2000_ | 1200_2030_ | 1300_1900_ | 1300_1930_ | 1300_2000_ | 1300_2100_ | 1300_2130_ | 1400_2000_ | 1400_2030_ | 1400_2130_ | 1400_2200_ | 1400_2230_ | 1500_2100_ | 1500_2130_ | 1500_2300_ | 1500_2330_ | 1600_2200_ | 1600_2230_ | 1600_2300_ | 1600_2300_ | 1600_0000_ | 1600_0030_ | 1700_2300_ | 1700_2330_ | |||
| 5 | Rate | $195.00 | $150.00 | $210.00 | $225.00 | $180.00 | $195.00 | $210.00 | $225.00 | $240.00 | $249.00 | $180.00 | $195.00 | $210.00 | $219.00 | $228.00 | $237.00 | $180.00 | $189.00 | $198.00 | $207.00 | $216.00 | $225.00 | $168.00 | $177.00 | $186.00 | $195.00 | $204.00 | $213.00 | $156.00 | $165.00 | $174.00 | $183.00 | $192.00 | $201.00 | $144.00 | $153.00 | $162.00 | $180.00 | $189.00 | $132.00 | $141.00 | $159.00 | $168.00 | $177.00 | $120.00 | $129.00 | $156.00 | $165.00 | $108.00 | $117.00 | $126.00 | $126.00 | $144.00 | $170.00 | $108.00 | $117.00 | |||
| 6 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | Time Query | 1600_2230_ | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 | Result | $ 170.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | * Result should be $117 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B8 | B8 | =INDEX($C$5:$BF$5,MATCH($B$7,$C$4:$BF$4)) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B7 | List | =$C$4:$BF$4 |
Apologies for the long Row 4 and Row 5, but it is part of my problem.
I thought I had formulated a simple Index and Match formula to find the 'Time Range' listed in B7. I even created the options with Data Validations to ensure no typos in data entry.
As you can see, the Index/Match returns the value of $170, when it should return $117 (I manually highlighted it in AZ5)
Every other time works in the formula. The only value of $170 is found in BD5.
What is bothering me is if I physically delete a column (almost any column), I get the correct output of $117.
I believe all Time Ranges are individual and non-duplicated.
I am sure I am missing something simple, or perhaps the eyes are too buggy, or maybe a limitation of Index/Match? But I certainly appreciate any advice!
Thanks in advance!
