I want to use the cell address reference for index and match to create an OFFSET formula to obtain the cell value in the next column for a calculation. Please see attached spreadsheet, cell references are filled "yellow".
=CELL("address",INDEX($Q$14:$Z$30,MATCH($C$4,$H$14:$H$30,0),1))
Tried various formula and can't work it out.
Thanks
what exactly are you trying to do here?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If your INDEX/MATCh formula retuns the Q17 value and you want the one from the next column then you can just increment the number at the end, e.g.
=INDEX($Q$14:$Z$30,MATCH($C$4,$H$14:$H$30,0),2)
That should give you the value from R17 - is that what you need?
Audere est facere
Hi FDibbins and daddylonglegs
Please see attached spreadsheet, I have outlined the cells/row with a "red" border. I would like to find the corresponding cell for the model match C4 in columns Q, T, W, Z and AC.
In the current sheet the cell reference I am looking for is Z17 because it is equal to C9.
I am trying to find a solution to obtain the adjacent cell AA17 so that I can use the value in the next formula.
The Index and Match in cell E9 is the prior step to the solution.
Hope the above and attached are a better explanation.
Thanks for your support.
See if this will do it for you...
=INDEX(OFFSET($H$13,MATCH($C$4,$H$14:$H$30,0),0,1,COUNTA(H13:AE13)),,MATCH(C9,OFFSET($H$13,MATCH($C$4,$H$14:$H$30,0),0,1,COUNTA(H13:AE13)),0)+1)
Your reference in F9 was pretty meaningless, but that formula will find and return ONE cell to the right of what you search for
Re: Find cell address reference for index and match to create use OFFSET for next column
Hi FDibbins
Nothing short of smashing!
Thank you very much for your support all I need to do now is study and understand the equation. So glad I joined the forum.
Kind regards
v.rodgers
Im happy it worked for you
I think you'd get the same result with this formula
=INDEX(I14:AE30,MATCH(C4,H14:H30,0),MATCH(C9,INDEX(I14:AE30,MATCH(C4,H14:H30,0),0),0)+1)
Thanks DDL, I knew there had to be an easier way
Hi daddylonglegs
That is certainly easier to understand. This forum is brilliant because you guys combine your knowledge and work together, fantastic and very much appreciated.
Thank you to both of you.
Kind regards
v.rodgers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks