Hi, in match formula can we specify the column range of a particular table i want to change the column range selection for different tables in same formula please reply
If you do that, you're cutting off the column with the emp_name. So, if you want to present the rows of that column dynamically, you need to also include the said column.
So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
No need for 2 match function =OFFSET($A$1;MATCH(G2;$D$2:$D$11;0);0;1;1) is enough. Or =OFFSET($D$1;MATCH(G2;$D$2:$D$11;0);-3;1) if You go from far right side. Or even better to use Index/match function =INDEX($A$2:$D$11;MATCH(G2;$D$2:$D$11;0);1)
Took me a while to get it but this is my understanding So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
So the Offset function formula is =Offset(Reference,Rows,Cols) Reference is the a cell reference to a cell containing the values that you are searching for. Rows decides the vertical Offset, negative values moves down and positive values move up. Cols decides the horizontal Offset, negative values moves left and positive values move right. So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down. And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
Best regards from Noida, keep up the good work
Thanks🙏
Thanks for the clarity. Helps for my application.
Glad it was helpful!
Thanks for Your help👍
Most welcome
Thank you my friend. This really helped me.
Most welcome🙏
Hi PK. Thanks for the great video. Was very helpful.
Thanks for your valuable feedback
Hello PK, Nice Job, well-done. How to offset a table not a range, and change the value of a cell in same raw?
Hi, in match formula can we specify the column range of a particular table
i want to change the column range selection for different tables in same formula
please reply
Great Pkji
Thanks for your valuable feedback
Hi PK, very good video, congrats!
Thanks for your valuable feedback
Very Helpful. Like your videos PK. Keep up the good work.
Thanks for your valuable feedback
Hello Pk, nice video. But, the second "-1" is useless. You can just put the range "b1:d1"
If you do that, you're cutting off the column with the emp_name. So, if you want to present the rows of that column dynamically, you need to also include the said column.
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
How its come -1 value ...?
Can u explain...?
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
Can you clarify the reason for the first "-1"?
For the Left hand side we will use - 1,-2 or whatever coloum number
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
How to use this in vba
3:07 what the function of -1 ? i still dont get it
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
thanks
Thanks for watching
No need for 2 match function =OFFSET($A$1;MATCH(G2;$D$2:$D$11;0);0;1;1) is enough. Or =OFFSET($D$1;MATCH(G2;$D$2:$D$11;0);-3;1) if You go from far right side. Or even better to use Index/match function =INDEX($A$2:$D$11;MATCH(G2;$D$2:$D$11;0);1)
this is what I need, but I have trouble understanding your accent. I wish you spoke southern:)
Half of the things you know but isn't explaining properly to the audience. Poor teaching method.
Took me a while to get it but this is my understanding
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.
didnot understand
So the Offset function formula is =Offset(Reference,Rows,Cols)
Reference is the a cell reference to a cell containing the values that you are searching for.
Rows decides the vertical Offset, negative values moves down and positive values move up.
Cols decides the horizontal Offset, negative values moves left and positive values move right.
So in the video the first match part finds the vertical position of the thing being searched for. Adding or subtracting from this will move the offset up or down.
And the second part finds the horizontal position of the thing being searched for. Adding or subtracting from this will move the offset right or left.