Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Hello,
I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Please diagram a sample of the data and the desired results.
-- Thanks, Shane Devenshire "DavidFarnsworth" wrote: Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Hi David
If I understand you correctly, then you will have a series of text values in A1:A19 You will have a series of Numbers in B1:B19 You have a series of text values in D1:L119 and you wish to retrieve the value in row 7 where (for example) A1 and B1 match the values in D1 and D2 If that is correct, the following array entered formula should achieve what you want {=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))} To produce an array formula, Commit or Edit using Control + Shift + Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Thank You!!! I'm just glad someone was able to decipher what I meant!
Thank YOU! David "Roger Govier" wrote: Hi David If I understand you correctly, then you will have a series of text values in A1:A19 You will have a series of Numbers in B1:B19 You have a series of text values in D1:L119 and you wish to retrieve the value in row 7 where (for example) A1 and B1 match the values in D1 and D2 If that is correct, the following array entered formula should achieve what you want {=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))} To produce an array formula, Commit or Edit using Control + Shift + Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Hi David
You're more than welcome. Thanks for the feedback to let us know that it was a solution that met your needs. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Thank You!!! I'm just glad someone was able to decipher what I meant! Thank YOU! David "Roger Govier" wrote: Hi David If I understand you correctly, then you will have a series of text values in A1:A19 You will have a series of Numbers in B1:B19 You have a series of text values in D1:L119 and you wish to retrieve the value in row 7 where (for example) A1 and B1 match the values in D1 and D2 If that is correct, the following array entered formula should achieve what you want {=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))} To produce an array formula, Commit or Edit using Control + Shift + Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
It did help indeed.
I have another question if you don't mind. I have a formula that provides part of the lookup for the previous formula you so kindly helped me with and I get the error value #VALUE! from some that don't quite meet the criteria. Could you explain how to leave a blank cell if the error value occurs with this formula... {=IF(OR(EXACT(X34,$N$34:$V$34)),MAX(HLOOKUP(X34,$N $34:$V$35,2,FALSE)),"")} Thanks again David "Roger Govier" wrote: Hi David You're more than welcome. Thanks for the feedback to let us know that it was a solution that met your needs. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Thank You!!! I'm just glad someone was able to decipher what I meant! Thank YOU! David "Roger Govier" wrote: Hi David If I understand you correctly, then you will have a series of text values in A1:A19 You will have a series of Numbers in B1:B19 You have a series of text values in D1:L119 and you wish to retrieve the value in row 7 where (for example) A1 and B1 match the values in D1 and D2 If that is correct, the following array entered formula should achieve what you want {=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))} To produce an array formula, Commit or Edit using Control + Shift + Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula help
Hi David
Try the non-array entered formula =IF(COUNTIF($N$34:$V$34,X34),MAX(HLOOKUP(X34,$N$34 :$V$35,2,FALSE)),"") -- Regards Roger Govier "DavidFarnsworth" wrote in message ... It did help indeed. I have another question if you don't mind. I have a formula that provides part of the lookup for the previous formula you so kindly helped me with and I get the error value #VALUE! from some that don't quite meet the criteria. Could you explain how to leave a blank cell if the error value occurs with this formula... {=IF(OR(EXACT(X34,$N$34:$V$34)),MAX(HLOOKUP(X34,$N $34:$V$35,2,FALSE)),"")} Thanks again David "Roger Govier" wrote: Hi David You're more than welcome. Thanks for the feedback to let us know that it was a solution that met your needs. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Thank You!!! I'm just glad someone was able to decipher what I meant! Thank YOU! David "Roger Govier" wrote: Hi David If I understand you correctly, then you will have a series of text values in A1:A19 You will have a series of Numbers in B1:B19 You have a series of text values in D1:L119 and you wish to retrieve the value in row 7 where (for example) A1 and B1 match the values in D1 and D2 If that is correct, the following array entered formula should achieve what you want {=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))} To produce an array formula, Commit or Edit using Control + Shift + Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Hello, I have a formula that results with 2 rows of data spread across 9 columns. The first row contains text. The second a value. (Text Text Text Text Text... 6 5 8 16 6... ) I have a column of all possible text (19) with a column beside it that looksup the text in the first row and retrieves the value below it and MAXs' the results. My next column I wish it to Index the 2 rows to retrieve the same column 9 and return a different value 5 rows down. I have been attempting to use index, match and offset IF the rows do not equal the same column with little success. I want to know s this possible? And if so, how would I go about it? Thank you for your help, David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
LOOKUP formula | Excel Worksheet Functions | |||
Populating a field based on lookup values | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions |