Hello OzGrid Members,
I'd like to drag down my formula while keeping everything as it is except the indexed column name (first part of the formula). Everything with desired results is in the attached file.
Thank you in advance.
Hello OzGrid Members,
I'd like to drag down my formula while keeping everything as it is except the indexed column name (first part of the formula). Everything with desired results is in the attached file.
Thank you in advance.
Hi,
Perhaps try this Array* formula (but please fix G2 & H2 to match the corresponding table exactly.. i.e. need a space between text and number).
=IFERROR(INDEX(Sheet1!$E$2:$G$12, MATCH($G$2&$H$2, Sheet1!$C$2:$C$12&Sheet1!$D$2:$D$12,0),ROWS($G$5:$G5)),"")
copied down
[arf]*[/arf]
Display MoreHi,
Perhaps try this Array* formula (but please fix G2 & H2 to match the corresponding table exactly.. i.e. need a space between text and number).
=IFERROR(INDEX(Sheet1!$E$2:$G$12, MATCH($G$2&$H$2, Sheet1!$C$2:$C$12&Sheet1!$D$2:$D$12,0),ROWS($G$5:$G5)),"")
copied down
[arf]*[/arf]
Hello NBVC thank you very much for your unique solution as always.
Just a little question: I got blank results. I guess it's related to something you said, but really couldn't identify the issue here. Where should I put a space in the sheet? Or where should I change so that formula works as expected?
Did you enter it correctly as an array forumul? CTRL+SHIFT+ENTER, not just ENTER.
Did you enter it correctly as an array forumul? CTRL+SHIFT+ENTER, not just ENTER.
Hello AliGW,
Yes, already done it as you offered.
NBVC's formula works for me, did you do as he said and, on Sheet2 cells G2 and H2, change Group1 and Budget1 to Group 1 and Budget 1, the values in those 2 cells must EXACTLY match the column headers in Sheet1.
NBVC's formula works for me, did you do as he said and, on Sheet2 cells G2 and H2, change Group1 and Budget1 to Group 1 and Budget 1, the values in those 2 cells must EXACTLY match the column headers in Sheet1.
Yes! KjBox, you all are right. My mistake, sorry all. Still can't believe how i am lucky to find all these best people here. Thank you NBVC, AliGW, and Kj!
Display MoreHi,
Perhaps try this Array* formula (but please fix G2 & H2 to match the corresponding table exactly.. i.e. need a space between text and number).
=IFERROR(INDEX(Sheet1!$E$2:$G$12, MATCH($G$2&$H$2, Sheet1!$C$2:$C$12&Sheet1!$D$2:$D$12,0),ROWS($G$5:$G5)),"")
copied down
[arf]*[/arf]
NBVC, just really wondering and want to know of it: How does this formula work? I mean, Row names do not change, then how does the formula get the next indexed column while everything is constant? Could you explain it for me? Thank you.
Hi,
The syntax for the INDEX() function is INDEX(array,row_num,[column_num])
The array can be one-dimensional (i.e. one column) or two-dimensional (i.e. multiple columns).
The row_num indicates which row number within the array you want to extract from
The column_num is required when the array is two-dimensional and indicates which column number to extract from. It is not required if array is one-dimensional)
So in my formula the array is two-dimensional (Sheet1!E2:G12), the row_num is found using the MATCH() function as you had done. We need to now indicate which columns within the array you want to pull out (we know that it would be column numbers 1, 2 and 3 for the 3 adjacent items you want to pull). Since we are dragging the formula down, in order to extract consecutive column numbers we can use the ROWS() function. ROWS($G$5:$G5) will result in 1, which means column 1, as you drag down it changes to ROWS($G$5:$G6) which results in 2, then ROWS($G$5:$G7) results in 3...
so the only thing that changes as you drag the formula down is the 3rd parameter of the INDEX function to extract the correct, consecutive column number.
Very very informative. Truth, i didn't get the point about ROW and what's amazing that two dimensional index has lighten up my mind now! I will build up new formulas. Thank you so much NBVC.
Here are a couple of sites to show you the INDEX function and it's capabilities especially in combination with the MATCH() function to find intersects.
http://www.ozgrid.com/Excel/index-match.htm
https://exceljet.net/excel-functions...index-function
Display MoreHere are a couple of sites to show you the INDEX function and it's capabilities especially in combination with the MATCH() function to find intersects.
http://www.ozgrid.com/Excel/index-match.htm
https://exceljet.net/excel-functions...index-function
I'm following up just right now. Thank you much
Don’t have an account yet? Register yourself now and be a part of our community!