Lookup value in table

[Stinney]

If I have a table like this:


1 103 205 309

2 110 254 378

3 178 298 401

and I want to lookup the closest match without going over a value based on the value in the first column and return the column number how can I do this?

For example:

If I want to return the closest match to 140 for values found in the row that has 1 in the first column. The result should be 2.

If I want to return the closest match to 300 for values found in the row that has 2 in the first column. The result should be 3.


- Stinney

Quoting only proves you know how to cut and paste.

 

[Kurupt55]

Im making a few assumptions here
1 your table is 4 columns wide
2 your values are always increasing from left to right

Make your table into a named range i have called it "data"
in F1 put your first filter value (ie 1, 2, 3)
In G1 put your second filter value (ie 140, 300)
then in H1 use this formula
=IF(VLOOKUP(F1,data,4)<G1,4,IF(VLOOKUP(F1,data,3)<G1,3,IF(VLOOKUP(F1,data,2)<G1,2,1)))
This will return 1 if you put in the values 1 and 100

If my assumptions are not correct this will more than likely not work.

Also there may be an easier solution but this is how i would go about it

Impossible is Nothing

 

[SkipVought]

Hi,

My table is in A24

My ROW value is in F2

My VALUE is in G2
[tt]
=MATCH(G2,OFFSET(A1,MATCH(F2,A2:A4,0),1,1,3),1)+1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}