| Home |
| Search |
| Today's Posts |
|
#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? |
| 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 | |||
Threaded Mode
