Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
Hello all,
I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
Small version:
=INDEX(G1:M1,MATCH("A",G2:M2,0)) If there's a possibility of "A" not being found: =IF(COUNTIF(G2:M2,"A"),INDEX(G1:M1,MATCH("A",G2:M2 ,0)),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Accesshelp" wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
=index(g1:m1,match("A",g2:m2,0))
Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions03.html (for =index(match())) Accesshelp wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
Hi.
I recommend trying a formula that combines INDEX and MATCH formulas. I consider this combination to be a breakthrough invention and the most powerful combination of finding and returning data between workbooks, sheets, or ranges on the sheet. Unlike VLOOKUP or HLOOKUP, the data do not have to be in sorted order. In combination, MATCH returns the row or column position of a cell based on the cell contents, and INDEX return the value of a cell based on column and or row positions identified in the MATCH statement. Enter this formula anywhere on the worksheet or add the sheet name where the data are stored to use the formula on another sheet. "=INDEX(3:3,,MATCH("A",2:2,0))" If "A" is in column D, the MATCH returns 4, and the formula resolved to the value in cell D3. Understand the formula from the inside parenthesis first: MATCH("A",2:2,0) finds a cell containing the first argument (i.e. "A") in the second argument (i.e. 2:2) and the third argument (ie. value of 0) is a switch saying find the exact match. Note that Match returns the position of the first cell containing "A" reading down the row. e.g. if "A" is in column D, the result of this MATCH is a value 4. INDEX(3:3,,4) returns the value of the cell in the range given as the first argument (i.e. row 3), at the row index position within the range given in the second argument (i.e. null because your reading only a row) and at the column inde position within the range given in the third argument (i.e. 4). This is a powerful formula with many nuances worth learning. The above formula used as written will solve your problem for now. Finally, notice that I wrote the formula to look in the entire row, not just the range given in your question. I find the formula is less prone to error this way/. However, if you are entering these formulas in columns A:G for example where "A" may occur before column G, use: "=INDEX(G3:M3,,MATCH("A",G2:M2,0))" When using this less than whole row or column references, you must remember to use the equivalent ranges for the match as in the index formula, since INDEX runs from relative position in the range. Good luck. "Accesshelp" wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
Luke,
Thank you very much for the formula. I end up using the second formula. You were very thoughtful. Have a great holiday season! "Luke M" wrote: Small version: =INDEX(G1:M1,MATCH("A",G2:M2,0)) If there's a possibility of "A" not being found: =IF(COUNTIF(G2:M2,"A"),INDEX(G1:M1,MATCH("A",G2:M2 ,0)),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Accesshelp" wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
ILoveExcel,
Thank you very much for the formula and detail explanations. Your formula and explanations work perfectly. Have a great holiday season! "ILoveExcel" wrote: Hi. I recommend trying a formula that combines INDEX and MATCH formulas. I consider this combination to be a breakthrough invention and the most powerful combination of finding and returning data between workbooks, sheets, or ranges on the sheet. Unlike VLOOKUP or HLOOKUP, the data do not have to be in sorted order. In combination, MATCH returns the row or column position of a cell based on the cell contents, and INDEX return the value of a cell based on column and or row positions identified in the MATCH statement. Enter this formula anywhere on the worksheet or add the sheet name where the data are stored to use the formula on another sheet. "=INDEX(3:3,,MATCH("A",2:2,0))" If "A" is in column D, the MATCH returns 4, and the formula resolved to the value in cell D3. Understand the formula from the inside parenthesis first: MATCH("A",2:2,0) finds a cell containing the first argument (i.e. "A") in the second argument (i.e. 2:2) and the third argument (ie. value of 0) is a switch saying find the exact match. Note that Match returns the position of the first cell containing "A" reading down the row. e.g. if "A" is in column D, the result of this MATCH is a value 4. INDEX(3:3,,4) returns the value of the cell in the range given as the first argument (i.e. row 3), at the row index position within the range given in the second argument (i.e. null because your reading only a row) and at the column inde position within the range given in the third argument (i.e. 4). This is a powerful formula with many nuances worth learning. The above formula used as written will solve your problem for now. Finally, notice that I wrote the formula to look in the entire row, not just the range given in your question. I find the formula is less prone to error this way/. However, if you are entering these formulas in columns A:G for example where "A" may occur before column G, use: "=INDEX(G3:M3,,MATCH("A",G2:M2,0))" When using this less than whole row or column references, you must remember to use the equivalent ranges for the match as in the index formula, since INDEX runs from relative position in the range. Good luck. "Accesshelp" wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a formula
Dave,
Thank you very much for the formula and additional references. Your formula works perfectly. Have a great holiday season! "Dave Peterson" wrote: =index(g1:m1,match("A",g2:m2,0)) Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions03.html (for =index(match())) Accesshelp wrote: Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up the value, instead of looking down. Thanks. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|