CODEWITHSUNDEEP
excelgoogle-sheetsoffset
viv227295
viv227295

Reputation: 397

How to return a certain number of rows just below the VLookup importrange output (in Google spreadsheet)?

For instance, if the following table is in sheet1 of spreadsheet 1 :

________A_____|__B__|__C__|
1 | Monitor   |  1  |  a  |
2 | Keyboard  |  2  |  e  |
3 | Headset   |  3  |  i  |
4 | HDD       |  4  |  o  |

The following formula in spreadsheet 2 : 

=Arrayformula(VLOOKUP("Monitor",importrange("spreadsheet1key", "sheet1!A1:C4",{2,3},FALSE)

returns "1" & "a".

Is there a way to return the content of 1 row below as well (of sheet1 of spreadsheet 1), that is, "2" & "e" along with "1" & "a" as shown in the table below?

________C_____|__D__|__E__|
1 |           |  1  |  a  |
2 |           |  2  |  e  |

Had the input and output data both been in the same spreadsheet the offset function could have achieved it easily through the following (thanx to @Max Makhrov)

=OFFSET(A1,MATCH("Monitor",Sheet1!A:A,0)-1,1,2,2)

But the importrange has to be used for some reason. And offset function currently does not work with importrange in Google spreadsheet.

Pulling the data from the source spreadsheet first through importrange function in one sheet in the target spreadsheet, and then using the above formula nested with 'offset' function in another sheet would have been one potential solution, but the problem is that due to some unavoidable reason, i need to get the result through one formula if possible.

In other words, i am looking for an opinion on this that if offset function currently does not work with importrange in Google spreadsheet, which other function/workaround with importrange could give the above needed result.

Thanx a lot for the response!

Upvotes: 0

Views: 725

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34305

Yes so I was right in my comment that you can do it by filter but you seem to have to repeat the import several times

    =filter(importrange("key","sheet1!A:C"),
    importrange("key","sheet1!B:B")>=match("Keyboard",importrange("key","sheet1!A:A"),0),
    importrange("key","sheet1!B:B")<=match("Keyboard",importrange("key","sheet1!A:A"),0)+1)

This assumes that column B already has numbers 1-4 in it and of course it will only give one row if you try and use it on the last row because there is no more data.

enter image description here

If you couldn't rely on column B having numbers 1-N in it, you would have to generate them by putting (say) row(z1:z4) in the formula (I think an array formula is required here but haven't tested it).

Upvotes: 1

Related Questions