0

Example of what I would like to achieve:

example

A  B  C   D  E  F
1  1  <=  2  2  1
2  2  <=  4  23 8
3  4  <=  7  5  3
4  7  <=  10 40 10
5  10 <=  12 7  3
6  12 <=  17 8  4
7  17 <=  19 12 5

I would like to take each number from the E column then determine to which row A it belongs to, if the criteria is that number E is >B and <=D (using the whole range B1:D999 that has different values in each row).

F column would then return the row number A for numbers from E column.

If every row of B and D columns are my criteria as in B1<=D1, B2<=D2, B3<=D3 etc., I would like to compare each number in column E and see in which range they fall into. Example would be E6=40, that number falls into B12<=D12 range that has a value of 10 in row A12). So i would like to get that number from A row in the cell F6 beside the E6. I have around 500 numbers and they also include decimal numbers in B,D and E columns and A column only has whole numbers.

A3 row means numbers from 1 to including 2, row A4 then means numbers bigger then 2 to including 4. So the number E3 is bigger then number in B3 column and smaller or equal then number in D3 column therefore it falls in the range in row 3, so the F value is the same as A3 = 1.

Improve this question
4
  • Sorry, but the logic of what you're trying to do escapes me. Please add an explanation.
    – fixer1234
    Aug 18, 2018 at 19:05
  • If every row of B and D columns are my criteria as in B1<=D1, B2<=D2, B3<=D3 etc., i would like to compare each number in column E and see in which range they fall into. Example would be E6=40, that number falls into B12<=D12 range that has a value of 10 in row A12). So i would like to get that number from A row in the cell F6 beside the E6. I have around 500 numbers and they also include decimal numbers in B,D and E columns and A column only has whole numbers. Hope this clears it up.
    – Gasper
    Aug 18, 2018 at 20:43
  • Every range starts with the same number that the previous range ended with. How do you decide which range to use for values that are at the boundary. e.g., why is F3 1 instead of 2?
    – fixer1234
    Aug 18, 2018 at 21:12
  • A3 row means numbers from 1 to including 2, row A4 then means numbers bigger then 2 to including 4. So the number E3 is bigger then number in B3 column and smaller or equal then number in D3 column therefore its the same as A3 = 1.
    – Gasper
    Aug 18, 2018 at 21:32

2 Answers 2

Reset to default
2

This is a variation on p.phidot's approach. Doing a lookup in the ascending direction catches the wrong range, and adjusting for mid-range values throws off results for values at the range boundary. A normal MATCH works if you do a descending lookup.

Sort the table on column A, B, or D descending. The formula in F3 would be:

=INDEX(A:A,MATCH(E3,D:D,-1),)

If there is other content in those columns, make the range references absolute addresses, like $A$3:$A$12 and $D$3:$D$12. This only needs columns A and D for lookup, so I didn't bother filling in the column B or C content in my example:

enter image description here

If your column A is just a table row identifier, and that can remain in ascending order, this can even be simplified. MATCH returns the sequence number in the table, which corresponds to column A in ascending order, so the formula can be reduced to:

=MATCH(E3,$D$3:$D$12,-1)

enter image description here

Note that for the MATCH result to match the column A table row index, the lookup range needs to be specified with absolute references.

Improve this answer
1
  • Good one @fixer1234,, perfect 10 ☺
    – Rajesh Sinha
    Aug 19, 2018 at 8:50
0

try this in F3 :

=IFERROR(INDEX(A:A,MATCH(E3,D:D,0)),INDEX(A:A,MATCH(E3,D:D,1))+1)

but I think you'll have to add :

A1  ---> -1
A2  ---> 0
D1  ---> 0
D2  ---> 1

to cover the 'missing range'.

Improve this answer
2
  • Hi phidot, while this is very close to what i was looking for, its just that if i have a decimal number in E column like 1.1 or 1.5 etc. i get a #N/A value in F column. Same problem if there are decimal numbers in criteria columns(B and D).Is there any way to include decimal numbers? Ty
    – Gasper
    Aug 18, 2018 at 20:18
  • did you noticed how this index() + match() works? It checks column E only it don't even read column B. since '1.5' & '1.1' is less than '2' in column E , so it is out of the match() range. Both match() with returns #N/A since column E doesn't have '1' to 'catch' it.|| have a read on the link I've shared... tell if you get stuck in understanding how it works. || btw ans updated ( :
    – p._phidot_
    Aug 19, 2018 at 3:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .