Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup worksheet function
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. -- Antonis1234 ------------------------------------------------------------------------ Antonis1234's Profile: http://www.excelforum.com/member.php...o&userid=28593 View this thread: http://www.excelforum.com/showthread...hreadid=482592 |
#2
|
|||
|
|||
Vlookup worksheet function
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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Antonis1234" wrote in message ... 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. -- Antonis1234 ------------------------------------------------------------------------ Antonis1234's Profile: http://www.excelforum.com/member.php...o&userid=28593 View this thread: http://www.excelforum.com/showthread...hreadid=482592 |
#3
|
|||
|
|||
Vlookup worksheet function
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" wrote in message ... 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. -- Antonis1234 ------------------------------------------------------------------------ Antonis1234's Profile: http://www.excelforum.com/member.php...o&userid=28593 View this thread: http://www.excelforum.com/showthread...hreadid=482592 |
#4
|
|||
|
|||
Vlookup worksheet function
You have other replies to your post in .programming.
Antonis1234 wrote: 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. -- Antonis1234 ------------------------------------------------------------------------ Antonis1234's Profile: http://www.excelforum.com/member.php...o&userid=28593 View this thread: http://www.excelforum.com/showthread...hreadid=482592 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a Macro to a VLookup Function | Excel Discussion (Misc queries) | |||
changing font style in a complex worksheet function | Excel Worksheet Functions | |||
How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |