Excel Conditional VLOOKUP - Switching Between Multiple Lookup Ranges
We all love LOOKUP functions in Excel and the heart favourite is VLOOKUP. We are well introduced to VLOOKUP and have already seen VLOOKUP in action several times, but we never came across a situation where we have multiple ranges to look up and have to switch between them based on a condition. For example, to calculate wages of workmen you have defined different rates depending on hours worked. Up to this point situation is easy as we can used VLOOKUP formula to easily fetch the rate corresponding to correct number of hours within data range. But what if you have different rates if labour force exceeds standard working hours? In this case you will have two ranges; one range for hours worked upto standard hours and second for hours worked above standard hours. So how can we make a lookup function to jump between two ranges and give us the right hourly rate? Solution The solution is easier than you think. As now we have a condition that “if hours worked are above standard hours ...