Find a Row/Index/VLookup for a number BETWEEN to values in 2 columns

[Joris Hermans]

Is there anyone out there who can help me with this one.
I've been looking around, and always came up with a (for me?) non-working solution.

Situation:
Column A contains a value. We need to find the ROW number (or VLOOKUP???) of the row, that contains the lower & upper boundires / values for my source value.

E.G.
A2; Value = 1,5
I need to look up in E2:E6 the row that contains the nearest under limit and in F2:F6 the nearest upper limit (or; the row for wicht the value of A2 falls in its range).
Then I need the value from G(ROW) to be put in B2

 

[GraH - Guido]

Sounds to me like a simple VLOOKUP with option true. Am I missing something?

In B2 =VLOOKUP(A2,$E$1:$G$6;3,TRUE)
drag down.

 

[bosco_yip]

Or……...

=LOOKUP(A2,E$2:E$6,G$2:G$6)

Regards
Bosco

 

[GraH - Guido]

Or……...

=LOOKUP(A2,E$2:E$6,G$2:G$6)

Regards
Bosco

I just don't have the reflex to think about that one. But indeed 1 argument less to use.

 

[Joris Hermans]

First of all, thanks for the fast reply's. But ... this is not what I'm looking for.
I've included a new excel example to make things clear.

Column A contains "Prices"
Column B needs to be filled up with a Percentage that can be found in a matrix (F:H).
If the value of Column A falls in between the range of Column F (minimum) and G (maximum), than the corresponding Column H (Percentage) must be taken to put in Column B
The sugested sollutions all ends up in "#N/A"

So, values in A do not have to match EXACTLY a value in Column F or G ... but fall in a range.

I could use plenty of nested "IF's, but if my lookup references (F:H) are composed of 100 lines, I defenitly will end up using over 64 nested IFs.

Hope someone can help me out.

 

[NARAYANK991]

Hi ,

I think this has already been mentioned , that the reason for the #N/A error is because the values in column A are text , and not numeric.

Change your formula to :

=VLOOKUP(A2+0,$F$2:$H$6,3,TRUE)

Narayan

 

[Joris Hermans]

Hi All
This works like a charm!
Thanks to y'all.