VLOOKUP and Ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wonder if you can help me.

I am trying to use VLOOKUP to do the following:

- I have a two column table, the first has words in it and the second has
percentages.
- What I want to do is lookup values (from a variety of different cells) and
match them to the percentages column - but bring back the words in the first
column.

The problem is that the values will not be an exact match - as basically the
percentages column is a list to define the ranges (i.e. it would say in the
first column a quarter and then the second column would say 24%-26%). What I
have done is entered "a quarter" twice and then put 24% in one and 26% in the
other (to define the range).

I need a formula though to pick up the relevant match.

Thanks.
 
Assuming the check percentage is in I2

=INDEX(A2:A20,MATCH(1,(B2:B20<=I2)*(D2:D20>=I2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob.

Thanks for this.

Could I just query which range D2:D20 is referring to, as I only have two
columns - A and B and then the cell that I am looking up I2
 
Back
Top