Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
I am trying to structure a LOOKUP formula which looks across a table of
values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
Try this array formula** :
A15 = lookup value = 11.9 =INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
Biff,
That came really close. While the array works great if the value exists, if the value does not exist it returns an error of #VALUE! from the formula. Is there any tweak to the formula which could be used to either leave this blank or insert a "Not Found" text string? Thanks for your help! "T. Valko" wrote: Try this array formula** : A15 = lookup value = 11.9 =INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
Try this (still array entered):
=IF(COUNTIF(B2:K11,A15),INDEX(A2:A11,MAX((B2:K11=A 15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1),"Not Found") -- Biff Microsoft Excel MVP "Marc" wrote in message ... Biff, That came really close. While the array works great if the value exists, if the value does not exist it returns an error of #VALUE! from the formula. Is there any tweak to the formula which could be used to either leave this blank or insert a "Not Found" text string? Thanks for your help! "T. Valko" wrote: Try this array formula** : A15 = lookup value = 11.9 =INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
That solved it. Thanks for your help!
"T. Valko" wrote: Try this (still array entered): =IF(COUNTIF(B2:K11,A15),INDEX(A2:A11,MAX((B2:K11=A 15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1),"Not Found") -- Biff Microsoft Excel MVP "Marc" wrote in message ... Biff, That came really close. While the array works great if the value exists, if the value does not exist it returns an error of #VALUE! from the formula. Is there any tweak to the formula which could be used to either leave this blank or insert a "Not Found" text string? Thanks for your help! "T. Valko" wrote: Try this array formula** : A15 = lookup value = 11.9 =INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Marc" wrote in message ... That solved it. Thanks for your help! "T. Valko" wrote: Try this (still array entered): =IF(COUNTIF(B2:K11,A15),INDEX(A2:A11,MAX((B2:K11=A 15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1),"Not Found") -- Biff Microsoft Excel MVP "Marc" wrote in message ... Biff, That came really close. While the array works great if the value exists, if the value does not exist it returns an error of #VALUE! from the formula. Is there any tweak to the formula which could be used to either leave this blank or insert a "Not Found" text string? Thanks for your help! "T. Valko" wrote: Try this array formula** : A15 = lookup value = 11.9 =INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function syntax across a table?
Hi,
You can try this as well. I have assumed that the data below in range C6:F9. In column enter nos. in ascending order starting from 1. =INDEX($C$6:$G$9,SUMPRODUCT(($D$6:$F$9=E12)*(G6:G9 )),1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Marc" wrote in message ... I am trying to structure a LOOKUP formula which looks across a table of values, and if it finds a particular value, it reports the ROW NAME's value. For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in Column A, followed by a matrix of numbers within the table B2:K11: Employee 1 2 3 4 5 6 7 8 9 10 E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0 E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8 E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9 E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7 E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2 E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6 E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4 E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3 E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1 E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5 19.5 I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which would allow me to search the table for a value (say "11.9") and return the value from Column A (in this case "E7"). Any idea how I should construct the function call? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Vlookup Using New Table Syntax | Excel Worksheet Functions | |||
Lookup : Syntax Error | Excel Worksheet Functions | |||
Variable Table Array in Lookup Function | Excel Worksheet Functions | |||
What is the syntax for referencing another sheet with 'lookup' | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |