Google Sheets: How to skip first VLOOKUP match and find next occurrence?

Other Questions Google Sheets
1

I’m stuck on a tricky Google Sheets problem. I’ve got a VLOOKUP formula that’s almost doing what I need, but not quite.

Here’s what I’m trying to do:

  1. Check if J80 matches a value from VLOOKUP
  2. If it matches, return the value from column T
  3. If it doesn’t match, I want to skip the first match of A80 and find the next one

My current formula looks like this:

=IF(J80=VLOOKUP(A80,IMPORTRANGE("SHEET_URL","Sheet!B:T"),11,FALSE),
VLOOKUP(A80,IMPORTRANGE("SHEET_URL","Sheet!B:T"),19,FALSE),"not found")

It works for the first part, but I can’t figure out how to skip the first match when J80 doesn’t match. I’ve tried messing around with INDEX, FILTER, and QUERY, but I’m stumped.

Any ideas on how to make this work? I’d really appreciate some help!

2

I encountered a similar challenge recently. One approach that worked for me was using a combination of FILTER and INDEX functions. Try this formula:

=INDEX(FILTER(IMPORTRANGE(“SHEET_URL”,“Sheet!T:T”), IMPORTRANGE(“SHEET_URL”,“Sheet!B:B”)=A80, IMPORTRANGE(“SHEET_URL”,“Sheet!K:K”)<>J80), 1)

This filters the range to only include rows where column B matches A80 and column K doesn’t match J80, then returns the first value from column T. It effectively skips the first match when J80 doesn’t match. Adjust column references as needed for your specific setup.

Let me know if this solves your problem or if you need further clarification.

3

hey ethan, i’ve dealt with similar issues. have u tried using array formulas? something like ARRAYFORMULA(VLOOKUP(A80,FILTER(IMPORTRANGE(…),J80<>Column11),19,FALSE)) might work. it filters out the first match based on J80. let me know if that helps!

4

I ran into a similar issue a while back. What worked for me was using a combination of QUERY and OFFSET. Here’s a formula you could try:

=QUERY(IMPORTRANGE(“SHEET_URL”,“Sheet!B:T”), “SELECT Col19 WHERE Col2 = '”&A80&“’ AND Col11 <> '”&J80&"’ LIMIT 1 OFFSET " & IF(J80=VLOOKUP(A80,IMPORTRANGE(“SHEET_URL”,“Sheet!B:T”),11,FALSE),0,1))

This formula first checks if J80 matches the VLOOKUP result. If it does, it returns the first match. If not, it uses OFFSET to skip the first occurrence and return the next one. The QUERY function allows for more flexible filtering.

Keep in mind that this might be a bit slower with large datasets due to the IMPORTRANGE function. If performance becomes an issue, you might want to consider using a helper column in your source sheet instead.