Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
article from Gord "Last Line" microsoft.public.excel.worksheet.functions
4/7/09 And from Biff "Need help finding the last cell w/data" 4/6/09 Sorry, I don't know how to accuire the link to the threads. Ok Ya'll, I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range). I can test and see that it works but I'd like to know how it works in order to take better advantage of it. The Microsoft documentation says that the first argument is a Lookup Value but I don't understand the logic of how it's being used in the examples above to find the last used cell or last Number entered in a range. Can someone point me to documentation that may help me better understand this usage. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
=LOOKUP(99^99,A:A)
tries to find 99 to the power of 99 which is a very very large number and unlikely to be in your lookuprange...so it tries to find it in the range and returns the last value it finds... which it presumes to be the largest since lookup range is supposed to be sorted... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Bassman62" wrote: article from Gord "Last Line" microsoft.public.excel.worksheet.functions 4/7/09 And from Biff "Need help finding the last cell w/data" 4/6/09 Sorry, I don't know how to accuire the link to the threads. Ok Ya'll, I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range). I can test and see that it works but I'd like to know how it works in order to take better advantage of it. The Microsoft documentation says that the first argument is a Lookup Value but I don't understand the logic of how it's being used in the examples above to find the last used cell or last Number entered in a range. Can someone point me to documentation that may help me better understand this usage. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
=LOOKUP(1E100,A1:D1)
...........A..........B..........C..........D 1....................10.........7............. Result = 7 How it works: If the lookup value is greater than *every* value in the referenced range the formula will return the *last* value from the range that is *less than* the lookup value. In the above the lookup value is 1E100 which is scientific notation for a very large number, 1 followed by 100 0s. There is a very good chance that no number in the range will be greater than 1E100. So, this meets the condition that the lookup value is greater than any value in the referenced range so the formula returns the *last* value in the range that is less than 1E100 which is the rightmost value in the range, 7. When someone posts this type of question in the forum we (almost always) don't know how big the numbers will be in the referenced range so we use an arbitrary number for the lookup value that is guaranteed to be greater than every value in the range. 1E100 is the arbitrary number that I use. Technically, you just need a number greater than the max number in the range. =LOOKUP(MAX(A1:D1)+1,A1:D1) This will do the same thing but it contains an extra function that really isn't needed. Sometimes this gets into what I consider to be overkill. For example, I use a lookup formula like this in my check register. I know for certain that the numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more realistic number as my lookup value. You might also see others use a lookup value of 9.99999999999999E+307. This is also a very large number in scientific notation. It's the largest number that Excel can handle. While that will work just fine I think it confuses most users. How many 9s do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use 1E100 and not have to bother counting 9s. Suppose you were looking up bowling scores. You know for a fact that the highest possible bowling score is 300. You can use one of these for your lookup value: 301 9.99999999999999E+307 I'll choose 301. 99^99 (or other similar expressions) will also work. It *calculates* a very large number to be used as the lookup value. But, if you don't need to use an extra calculation why do it? -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... article from Gord "Last Line" microsoft.public.excel.worksheet.functions 4/7/09 And from Biff "Need help finding the last cell w/data" 4/6/09 Sorry, I don't know how to accuire the link to the threads. Ok Ya'll, I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range). I can test and see that it works but I'd like to know how it works in order to take better advantage of it. The Microsoft documentation says that the first argument is a Lookup Value but I don't understand the logic of how it's being used in the examples above to find the last used cell or last Number entered in a range. Can someone point me to documentation that may help me better understand this usage. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
Thank you very much.
With this info, I re-read the MS docs and think I'm beginning to get it. MS doc. "...and return a value from the - same position - ..." (last position found with a value) I shall carry on. Thanks again. "T. Valko" wrote in message ... =LOOKUP(1E100,A1:D1) ..........A..........B..........C..........D 1....................10.........7............. Result = 7 How it works: If the lookup value is greater than *every* value in the referenced range the formula will return the *last* value from the range that is *less than* the lookup value. In the above the lookup value is 1E100 which is scientific notation for a very large number, 1 followed by 100 0s. There is a very good chance that no number in the range will be greater than 1E100. So, this meets the condition that the lookup value is greater than any value in the referenced range so the formula returns the *last* value in the range that is less than 1E100 which is the rightmost value in the range, 7. When someone posts this type of question in the forum we (almost always) don't know how big the numbers will be in the referenced range so we use an arbitrary number for the lookup value that is guaranteed to be greater than every value in the range. 1E100 is the arbitrary number that I use. Technically, you just need a number greater than the max number in the range. =LOOKUP(MAX(A1:D1)+1,A1:D1) This will do the same thing but it contains an extra function that really isn't needed. Sometimes this gets into what I consider to be overkill. For example, I use a lookup formula like this in my check register. I know for certain that the numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more realistic number as my lookup value. You might also see others use a lookup value of 9.99999999999999E+307. This is also a very large number in scientific notation. It's the largest number that Excel can handle. While that will work just fine I think it confuses most users. How many 9s do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use 1E100 and not have to bother counting 9s. Suppose you were looking up bowling scores. You know for a fact that the highest possible bowling score is 300. You can use one of these for your lookup value: 301 9.99999999999999E+307 I'll choose 301. 99^99 (or other similar expressions) will also work. It *calculates* a very large number to be used as the lookup value. But, if you don't need to use an extra calculation why do it? -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... article from Gord "Last Line" microsoft.public.excel.worksheet.functions 4/7/09 And from Biff "Need help finding the last cell w/data" 4/6/09 Sorry, I don't know how to accuire the link to the threads. Ok Ya'll, I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range). I can test and see that it works but I'd like to know how it works in order to take better advantage of it. The Microsoft documentation says that the first argument is a Lookup Value but I don't understand the logic of how it's being used in the examples above to find the last used cell or last Number entered in a range. Can someone point me to documentation that may help me better understand this usage. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
99^99 is also a very large number so that's why you will see it used as
well. Gord On Tue, 7 Apr 2009 18:10:39 -0400, "T. Valko" wrote: =LOOKUP(1E100,A1:D1) ..........A..........B..........C..........D 1....................10.........7............. Result = 7 How it works: If the lookup value is greater than *every* value in the referenced range the formula will return the *last* value from the range that is *less than* the lookup value. In the above the lookup value is 1E100 which is scientific notation for a very large number, 1 followed by 100 0s. There is a very good chance that no number in the range will be greater than 1E100. So, this meets the condition that the lookup value is greater than any value in the referenced range so the formula returns the *last* value in the range that is less than 1E100 which is the rightmost value in the range, 7. When someone posts this type of question in the forum we (almost always) don't know how big the numbers will be in the referenced range so we use an arbitrary number for the lookup value that is guaranteed to be greater than every value in the range. 1E100 is the arbitrary number that I use. Technically, you just need a number greater than the max number in the range. =LOOKUP(MAX(A1:D1)+1,A1:D1) This will do the same thing but it contains an extra function that really isn't needed. Sometimes this gets into what I consider to be overkill. For example, I use a lookup formula like this in my check register. I know for certain that the numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more realistic number as my lookup value. You might also see others use a lookup value of 9.99999999999999E+307. This is also a very large number in scientific notation. It's the largest number that Excel can handle. While that will work just fine I think it confuses most users. How many 9s do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use 1E100 and not have to bother counting 9s. Suppose you were looking up bowling scores. You know for a fact that the highest possible bowling score is 300. You can use one of these for your lookup value: 301 9.99999999999999E+307 I'll choose 301. 99^99 (or other similar expressions) will also work. It *calculates* a very large number to be used as the lookup value. But, if you don't need to use an extra calculation why do it? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
To return a value from the same position in a different range:
Same principle, you just include the range where the result will come from. ...........A..........B..........C..........D 1......Jan........Feb.......Mar......Apr 2...................10...........7.............. =LOOKUP(1E100,A2:D2,A1:D1) Result = Mar Lookup the big num in A2:D2 and return the corresponding value from A1:D1. -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Thank you very much. With this info, I re-read the MS docs and think I'm beginning to get it. MS doc. "...and return a value from the - same position - ..." (last position found with a value) I shall carry on. Thanks again. "T. Valko" wrote in message ... =LOOKUP(1E100,A1:D1) ..........A..........B..........C..........D 1....................10.........7............. Result = 7 How it works: If the lookup value is greater than *every* value in the referenced range the formula will return the *last* value from the range that is *less than* the lookup value. In the above the lookup value is 1E100 which is scientific notation for a very large number, 1 followed by 100 0s. There is a very good chance that no number in the range will be greater than 1E100. So, this meets the condition that the lookup value is greater than any value in the referenced range so the formula returns the *last* value in the range that is less than 1E100 which is the rightmost value in the range, 7. When someone posts this type of question in the forum we (almost always) don't know how big the numbers will be in the referenced range so we use an arbitrary number for the lookup value that is guaranteed to be greater than every value in the range. 1E100 is the arbitrary number that I use. Technically, you just need a number greater than the max number in the range. =LOOKUP(MAX(A1:D1)+1,A1:D1) This will do the same thing but it contains an extra function that really isn't needed. Sometimes this gets into what I consider to be overkill. For example, I use a lookup formula like this in my check register. I know for certain that the numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more realistic number as my lookup value. You might also see others use a lookup value of 9.99999999999999E+307. This is also a very large number in scientific notation. It's the largest number that Excel can handle. While that will work just fine I think it confuses most users. How many 9s do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use 1E100 and not have to bother counting 9s. Suppose you were looking up bowling scores. You know for a fact that the highest possible bowling score is 300. You can use one of these for your lookup value: 301 9.99999999999999E+307 I'll choose 301. 99^99 (or other similar expressions) will also work. It *calculates* a very large number to be used as the lookup value. But, if you don't need to use an extra calculation why do it? -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... article from Gord "Last Line" microsoft.public.excel.worksheet.functions 4/7/09 And from Biff "Need help finding the last cell w/data" 4/6/09 Sorry, I don't know how to accuire the link to the threads. Ok Ya'll, I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range). I can test and see that it works but I'd like to know how it works in order to take better advantage of it. The Microsoft documentation says that the first argument is a Lookup Value but I don't understand the logic of how it's being used in the examples above to find the last used cell or last Number entered in a range. Can someone point me to documentation that may help me better understand this usage. Thank you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & MATCH syntax ??
Thanks very much to you all.
This helps a great deal. "Gord Dibben" wrote: 99^99 is also a very large number so that's why you will see it used as well. Gord On Tue, 7 Apr 2009 18:10:39 -0400, "T. Valko" wrote: =LOOKUP(1E100,A1:D1) ..........A..........B..........C..........D 1....................10.........7............. Result = 7 How it works: If the lookup value is greater than *every* value in the referenced range the formula will return the *last* value from the range that is *less than* the lookup value. In the above the lookup value is 1E100 which is scientific notation for a very large number, 1 followed by 100 0s. There is a very good chance that no number in the range will be greater than 1E100. So, this meets the condition that the lookup value is greater than any value in the referenced range so the formula returns the *last* value in the range that is less than 1E100 which is the rightmost value in the range, 7. When someone posts this type of question in the forum we (almost always) don't know how big the numbers will be in the referenced range so we use an arbitrary number for the lookup value that is guaranteed to be greater than every value in the range. 1E100 is the arbitrary number that I use. Technically, you just need a number greater than the max number in the range. =LOOKUP(MAX(A1:D1)+1,A1:D1) This will do the same thing but it contains an extra function that really isn't needed. Sometimes this gets into what I consider to be overkill. For example, I use a lookup formula like this in my check register. I know for certain that the numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more realistic number as my lookup value. You might also see others use a lookup value of 9.99999999999999E+307. This is also a very large number in scientific notation. It's the largest number that Excel can handle. While that will work just fine I think it confuses most users. How many 9s do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use 1E100 and not have to bother counting 9s. Suppose you were looking up bowling scores. You know for a fact that the highest possible bowling score is 300. You can use one of these for your lookup value: 301 9.99999999999999E+307 I'll choose 301. 99^99 (or other similar expressions) will also work. It *calculates* a very large number to be used as the lookup value. But, if you don't need to use an extra calculation why do it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP function syntax across a table? | Excel Worksheet Functions | |||
Lookup : Syntax Error | Excel Worksheet Functions | |||
What is the syntax for referencing another sheet with 'lookup' | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Excel Match value_lookup asterisk syntax | Excel Worksheet Functions |