Similar question seems to be this. However in my case I need an additional step (I think). What I can't get done is combine these two steps.
Let's say the data structure is as below
Col A | Col B | Col C | Col D | Col E |
Row 1 Val1 | Val2 | Val3 |
Row 2 Val4 | Val5 | Val6 |
Row 3 Val7 | Val8 | Val9 | Val10 | Val11
The formula I am looking for should return results as per below scenerios.
1) In a separate cell, If I enter a value (Val4); Do a VLOOKUP for Val4 in Col A (which is in Row 2) and then return the right most value which is Val6 from Col C
2) In a separate cell, If I enter a value (Val7); Do a VLOOKUP for Val7 in Col A (which is in Row 3) and then return the right most value which is Val10 from Col D
Basically the column values may grow to the right. So I need to lookup the first value -> get the row -> get the rightmost value in the same row....
Any help is much appreciated.
EDIT - 1
After the guidance given here, I came halfway to the solution. First thing to mention is that values in a row can grow (In my sample data set it goes up to column D. But it can grow to Column E,F etc... in any row)
Now, let's assume the value I am searching is in H1. I put Val4 in H1 and the formula:
OFFSET(A1,MATCH($H$1,$A:$A,0),2,1,1). //Since I have hard coded 3rd parameter to 2; it'll give me Val6 (the last column in that row)
Here by MATCH($H$1,$A:$A,0) it returns the row. In my example it'll return Row 2 since I am searching for Val4 (In H1)
Now, I should search columns in Row 2 and stop in ColC since its the last value in Row 2.
In the OFFSET function, 3rd parameter looks for the number of columns. So if I can vary that value based on the row; problem is solved. For example; if I search for Val7, in my OFFSET formula, it'll first find Row 3. But now in OFFSET 3rd parameter must 4 (to get the last value - Val11).
As I search the rows using MATCH($H$1,$A:$A,0); if I can pass that row to CountA() problem is solved like:
CountA(MATCH($H$1,$A:$A,0) : MATCH($H$1,$A:$A,0))
This is invalid. So now trying to use INDIRECT() function like:
COUNTA(INDIRECT(MATCH($H$1,$A:$A,0)&":"&MATCH($H$1,$A:$A,0)))
which basically builds row:row as a string and feeds to CountA()
This works on its own, but when I put this to OFFSET() function 3rd parameter, it doesn't work the way it should.
EDIT - 2 (ANSWER)
Got it to work with formula
OFFSET(A1,MATCH($H$1,$A:$A,0)-1,COUNTA(INDIRECT(MATCH($H$1,$A:$A,0)&":"&MATCH($H$1,$A:$A,0)))-1,1,1)
Only issue is when searching for columns in a row, if you have an empty cell it won't search further using COUNTA().... but otherwise it works the way I want
