Dual Look up: Vlookup, index or match

Topic

New Reply

I have struggled mightly with this assignment….Assume the following grid:

Code:

5	   0  Below Minimum Range
5  36,800  First Quartile
5  41,400  Second Quartile
5  46,000  Third Quartile
5  50,600  Fourth Quartile
5  55,201  Above Maximum Range
4	   0  Below Minimum Range
4  32,000  First Quartile
4  36,000  Second Quartile
4  40,000  Third Quartile
4  44,000  Fourth Quartile
4  48,001  Above Maximum Range

The first column is the pay code (i.e. the 5’s and 4’s). The second column is base compensation threshold amount and the third column is the commentary that I want to retrieve to a cell when looking up values in the first 2 columns.
EDIT: Somehow when I type this it looks like coulmns…but when it posts in the lounge it is run together

For example, if someone is a grade 5 and making less than 36,800, I need to retrieve the value in the third column, “Below Minimum Range”. If someone is a grade 5 and making 41,399, then I would retrieve the 3rd column value of “First Quartile”. I actually have about 20 pay grades with the 6 amounts if this impacts your recommendation.

I “played” the last 3 hours with vlookup, index and match and have only produced #NA’s, #Value’s and other various sundry Excel error messages….The only thing I believe that I have concluded [hopefully correctly] is that what I need to do cannot be accomplished with a vlookup function and nested if statements…I ran up against the if statement limit.

This caused me try to use match function, then the index function and finally a combination of the index with match function…but to no available. I have experimented with the true or false switch in these functions too. I have never used these functions before and could not apply some of the stuff I searched in the forum to successfully produce the required result.

There really should be a courtesy function coder in EXCEL that helps one out after he has produced 100+ error messages trying to apply a formula / function in one session…I have certainly exhausted my know-how [this was in minutes] and now my patience [this in 3 hours] with the so-called help file.

Any assistance is greatly appreciated. Thank you.

Reply | Quote

Replies

Say that the data you posted are in A1:C12, and that you enter the grade to be looked up in H1 and the wage in H2.
The description is given by the formula

=VLOOKUP(H2,OFFSET(B1:C6,MATCH(H1,A1:A12,0)-1,0),2)

B1:C6 is the lookup range for grade 5.
MATCH(H1,A1:A12,0) returns the row number where the grade is first found in column A.
OFFSET(B1:C6,MATCH(H1,A1:A12,0)-1,0) is the lookup range to be used.

See attached workbook.

Reply | Quote

An alternative approach to that offered by Hans is offered in the attached file. With this approach it is necessary to create a named range for each pay category.

Reply | Quote

Hans and wdwells..thank you both for a reply…I will study both examples as I really need to master these functions as I can now see how powerful they can be…I have especially struggle as the pay code column is always an exact match but the compensation match is to a “range” and determining how EXCEL handles this situation [i.e. not an exact match] has been extremely difficult for me. Again, thank you both.

Reply | Quote

FWIW, you could also rearrange your data table a bit, as in the attached.

Reply | Quote