VLOOKUP Problem formula not vba

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?

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

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)

Is your data in strict ascending order?

Reverse the order of the table and use

Code:

---

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

---

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:

Code:

---

=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?

Code:

---

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

---

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

Cheers

What I am suggesting is

Code:

---

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

---

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

Code:

---

=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:

Code:

---

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

---

it would read

Code:

---

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

---

and:

Code:

---

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

---

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

Code:

---

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

---

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

:-)

Problem solved!