I am having trouble constructing a unusual lookup formula in Excel.
Normally, I would use VLOOKUP(), but the pitfall is that I want to look up a number in a column from a table that is sorted on an other column. Vlookup can only lookup things on the sorted column itself.
The scenario is as follows:
- The table is sorted ascending on column B.
- For a given parameter P, I now want to find the first A value, starting from the top and going down, that is bigger than or equal to that parameter P.
- The function should then return the corresponding B value.
Table(part off, the complete table is much bigger):
A B
1 640 4.5
2 1600 7.0
3 640 7.5
4 1280 12.0
5 1920 16.5
6 2560 21.0
7 1600 19.8
8 3200 26.8
9 4800 33.8
For example, say my parameter is 1100, then I want my formula to return 7.0, because the first entry when searching downwards in column A that is bigger than or equal to 1100 is 1600, which has a corresponding B value of 7.0
I ve tried my luck with Array Formula s (also known as "ctrl-shift-enter formula s") and constructed something like this:
{=INDEX(table;
MATCH(MIN(IF(columnA-$C1>=0;columnA;FALSE));
IF(columnA-$C1>=0;columnA;FALSE);
0);
2)}
with C1 containing my parameter, table the range A1:B9, and columnA the range B1:B9
But that doesn t work (in the example above, it returns 12.0 because 1280 is selected by the MIN() function) .
Solution I don t want to use: I could write some VBA could to go through the table, but I don t want to do that (because of annoying macro warnings , and because Excel on Mac doesn t support VBA anymore)
Anybody any clues?