VLOOKUP Problem formula not vba

[ukdane]

I have a worksheet that uses decimals in one sheet, and looks up a value based on the decimal in a table.
However if it doesn't find the result, it shows rounded down result (the last row in the table), and I need it to show the rounded up result (the next row in the table).

Any ideas?

[Bob Phillips]

What does the data look like, and what formula are youusing, an exact or approximate match? AN exact match works fine here.

[ukdane]

For example,
In the data I have A1 = 0.5 B1 = 0.6 C1=1

In the array, it needs to look up the values A1, B1, and C1.
But the array only has the following values.
0
0.5
1

If it looks up 0.6, it will return the value found in the 0.5 field. I need it to return the value in the 1 field.

The other part of the problem is that the various arrays don't all contain the same numbers, so one array might well contain array
0
0.5
1
but another array might only include
5
10
20
(in which case the above 3 examples should all show the value for 5, and not N/A)

[mdmackillop]

Is your data in strict ascending order?

[Bob Phillips]

Reverse the order of the table and use


=INDEX(L1:L3,MATCH(2.5,K1:K3,-1))

[ukdane]

xld: In your example: =INDEX(L1:L3,MATCH(2.5,K1:K3,-1))

I don't know what the index is exactly, as it is generated by another cell.
For example, the formula could read:

=INDEX(L1:L3,MATCH(2.5,K1:K3,-1))
=INDEX(M1:M3,MATCH(2.5,K1:K3,-1))
=INDEX(N1:N3,MATCH(2.5,K1:K3,-1))

based on the results of another lookup table elsewhere, that returns either L, M, or N.
How do I add the L, M, or N to where I've placed the X below?


=INDEX(X1:X3,MATCH(2.5,K1:K3,-1))

(Just to make it even more complicated, X is on another worksheet).

Cheers

[Bob Phillips]

What I am suggesting is


=INDEX(column_to_extract_value from,MATCH(lookup_value,column_to_check_lookup_value,-1))

[ukdane]

xld: Yes, I appreciate that.
however I don't know how to write the following code correctly:

=INDEX('Sheet2'!X$22:X$51;MATCH(AI2;D$22:D$51;-1))

Where X is the result of another column AZ.
AZ contains the result of a lookup table for example
AZ1 = G
AZ2 = K

so in the above example:

=INDEX('Sheet2'!X$22:X$51;MATCH(AI2;D$22:D$51;-1))

it would read

=INDEX('Sheet2'!(result of AZ1)$22:(result of AZ1)$51;MATCH(AI1;D$22:D$51;-1))

and:

=INDEX('Sheet2'!(result of AZ2)$22:(result of AZ2)$51;MATCH(AI2;D$22:D$51;-1))

[ukdane]

Those shouldn't be smilies.
It should be : followed by D

[Bob Phillips]

=INDEX(INDIRECT(Sheet2!AZ1&"22:"&Sheet2!AZ1&"51");MATCH(AI1;D$22:D$51;-1))

[ukdane]

Thanks XLS, I realised I needed to use INDIRECT function just before you posted (I always forget what it's called!)

:-)

Problem solved!