|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Lookup Formula help
Hi David
I wasn't paying full attention in my last post. The MAX function in your original formula is superfluous. The Hlookup will only return the first instance of X34 within the range, hence Max is working on a single value and doesn't achieve anything. Change the formula to =IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$ 35,2,FALSE),"") -- Regards Roger Govier "Roger Govier" wrote in message ... 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 |
Lookup Formula help
Not quite what I need. In the lookup range are multiples of the lookup value
and it only returns the first encountered. I need to find the largest. Thank you. David "Roger Govier" wrote: Hi David I wasn't paying full attention in my last post. The MAX function in your original formula is superfluous. The Hlookup will only return the first instance of X34 within the range, hence Max is working on a single value and doesn't achieve anything. Change the formula to =IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$ 35,2,FALSE),"") -- Regards Roger Govier "Roger Govier" wrote in message ... 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 |
Lookup Formula help
Hi David
Then try =IF(COUNTIF($N$34:$V$34,X34),MAX(($N$34:$V$34=X34) *$N$35:$V$35),"") -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Not quite what I need. In the lookup range are multiples of the lookup value and it only returns the first encountered. I need to find the largest. Thank you. David "Roger Govier" wrote: Hi David I wasn't paying full attention in my last post. The MAX function in your original formula is superfluous. The Hlookup will only return the first instance of X34 within the range, hence Max is working on a single value and doesn't achieve anything. Change the formula to =IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$ 35,2,FALSE),"") -- Regards Roger Govier "Roger Govier" wrote in message ... 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 |
Lookup Formula help
That did it! Thanks for your help and your patience!
David "Roger Govier" wrote: Hi David Then try =IF(COUNTIF($N$34:$V$34,X34),MAX(($N$34:$V$34=X34) *$N$35:$V$35),"") -- Regards Roger Govier "DavidFarnsworth" wrote in message ... Not quite what I need. In the lookup range are multiples of the lookup value and it only returns the first encountered. I need to find the largest. Thank you. David "Roger Govier" wrote: Hi David I wasn't paying full attention in my last post. The MAX function in your original formula is superfluous. The Hlookup will only return the first instance of X34 within the range, hence Max is working on a single value and doesn't achieve anything. Change the formula to =IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$ 35,2,FALSE),"") -- Regards Roger Govier "Roger Govier" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com