Vlookup worksheet function

A

Antonis1234

Hi,

I am using the vlookup function in order to find prices of my products
from a column of a worksheet.

But I need to make vlookup to search from bottom to top and not the
other way around.

How can I do that?

Thanks in advance,

Antonis.
 
M

Max

One play is to invert the lookup table ..

Assuming the vlookup table is in A1:B10

10 19
9 14
8 13
7 13
6 14
5 19
4 18
3 15
2 20
1 20

we could invert the table by putting in say, D1:
=INDEX(A:A,MATCH(SMALL($A:$A,ROW()),$A:$A,0))
copy D1 across to E1, then fill down to E10

This yields:

1 20
2 20
3 15
4 18
5 19
6 14
7 13
8 13
9 14
10 19

Then we could use VLOOKUP in the normal manner
by pointing at cols D & E (instead of cols A & B)
 
N

Niek Otten

Hi Antonis,

Depends on what you mean with "from bottom to top". Unfortunately you didn't
give us your formula.
If you mean the table is sorted from large to small; no problem, as long as
the 4th argument of the VLOOKUP is FALSE. You probably need that anyway,
otherwise you will get a misleading result if you to try to look up a
product which isn't in your list.
If you mean looking up takes too long (which could be the case if you do
many lookups in table of thousands of rows) and the product table is sorted
descending, you can do (at least) two things.
One is to sort the table ascending.
If that is not possible, you can use a combination of MATCH() and INDEX().
If you're absolutely sure you will never try to lookup a product which is
not in your list, you can use:

=INDEX(B1:B5,MATCH(E1,A1:A5,-1))

where B1:B5 is your prices, A1:A5 your product numbers and E1 the product
number to be looked up.

But I would never do that: I'd always check that the product exists:

=IF(INDEX(A1:A5,MATCH(E1,A1:A5,-1))=E1,INDEX(B1:B5,MATCH(E1,A1:A5,-1)),"WRONG
NUMBER!")

--
Kind regards,

Niek Otten

"Antonis1234" <[email protected]>
wrote in message
news:[email protected]...
 
Top