1

I really need some help with this.

I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in Column C2.

My case is similar to this Finding Last Instance but two lookup value.

This is what in Sheet1.

enter image description here

This is what in sheet2.

enter image description here

Tried this formula: =if(isblank(A2),"--",max(filter(row(Sheet2),Sheet2=A2)))

The formula is working but it is only for 1 value which is in A2.

So for example, since I am looking for last instance of A2: B2 in Sheet2, it should return the row number 20

I really want to share a sample sheet, but the company restrictions do not allow me to do so.

Recommendations and advice will be truly appreciated. And thank you in advance as well.

Improve this question
1
  • Welcome. Please show us what you mean. I am personally kind of lost with just the word explaining. Also, write couple of the formulas you tried.
    – marikamitsos
    Commented May 29, 2020 at 8:18

1 Answer 1

Reset to default
1

Please use the following formula: 

=QUERY( 
     ARRAY_CONSTRAIN( 
       SORTN({Sheet2!B2:B26&Sheet2!C2:C26, row(Sheet2!A2:A26),Sheet2!A2:A26},2,3,3,0),6,3), 
        "select Col2")

In our formula we create a virtual column Sheet2!B2:B26&Sheet2!C2:C26 by joining the two columns together, thus creating unique values.
We also use an extra column row(Sheet2!A2:A26) in order to return the corresponding rows values. The column we use makes no difference as long as it is in the range of 2-26.

enter image description here

Functions used:

Improve this answer
3
  • Great. "I really want to share a sample sheet, but the company restrictions do not allow me to do so.". Please read how to and share a test sheet so as you can be easier helped. You can prepare a new file. And it doesn't even be from the office. Demo sheets save masses of time to ones trying to help you. Have a nice weekend.
    – marikamitsos
    Commented May 30, 2020 at 18:45
  • If still in trouble, do share a demo sheet as explained before.
    – marikamitsos
    Commented Jun 1, 2020 at 2:09
  • 1
    Hi, it's working fine. However, if the lookup file is unsorted, it's not returning the right results.
    – Xan Mei
    Commented Jun 4, 2020 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.