0

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

user2058413
  • 691
  • 3
  • 10
  • 28

2 Answers2

0

Assume your data housed in A1:D3

Criteria put in A5:A6, (layout similar to JvdV's table)

If your data is text value, formula in B5 copied down :

=LOOKUP("zzz",INDEX($1:$3,SUMPRODUCT(($1:$3=A5)*ROW(A$1:A$3)),0))

If your data is numeric value, formula in B5 copied down :

=LOOKUP(9.9E+307,INDEX($1:$3,SUMPRODUCT(($1:$3=A5)*ROW(A$1:A$3)),0))

If your data is mixed in text+numeric value, formula in B5 copied down :

=INDEX($1:$3,SUMPRODUCT(($1:$3=A5)*ROW(A$1:A$3)),AGGREGATE(14,6,COLUMN($1:$1)/(INDEX($1:$3,SUMPRODUCT(($1:$3=A5)*ROW(A$1:A$3)),0)<>""),1))
bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • well, I managed to go halfway with a simpler formula. =OFFSET(A1,MATCH($H$1,$A:$A,0)-1,7,1,1). In this; MATCH($H$1,$A:$A,0) search for the value given in $H$1 in Column A:A and returns the row number and reduce by 1 to feed it to OFFSET(). At this point I have the correct row that I need to search. Then in OFFSET if I can make the 3rd parameter to work with COUNTA() using the row I found, I am done. But I can't do COUNTA(MATCH($H$1,$A:$A,0)-1 : MATCH($H$1,$A:$A,0)-1) which essentially is COUNTA(row:row) ... – user2058413 Nov 09 '19 at 13:17
  • In this formula =OFFSET(A1,MATCH($H$1,$A:$A,0)-1,7,1,1) If in the portion of MATCH(H1,A:A,0) return 4, the formula will offset by 7 columns & return the result in H4, which is no sense in using COUNTA for the 3rd parameter. What do you wanted? – bosco_yip Nov 09 '19 at 13:41
  • Please see my EDIT-1 to the main question. I am almost there... just that something is wrong with INDIRECT() ..... – user2058413 Nov 09 '19 at 14:19
  • Beware that offset is volatile. So is Indirect – JvdV Nov 09 '19 at 15:52
0

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

user2058413
  • 691
  • 3
  • 10
  • 28