google sheets - VLOOKUP IMPORTRANGE to select the column number in another file also - Stack Overflow

admin管理员组

文章数量:1334677

I want to vlookup importrange the data in another file and the specific column number will be selected in the another file because if ever there are changes in the column/field in the main file if will not affect.

Even I added a new column, I want to not affect the column number of the status and remarks field

=Arrayformula(IFERROR(VLOOKUP($A5:A,IMPORTRANGE("#gid=0","Sheet1!A3:H"),**ROW1 SELECTED IN THE SOURCE FILE**,0)))

SOURCE FILE:

TARGET FILE: #gid=0

I want to vlookup importrange the data in another file and the specific column number will be selected in the another file because if ever there are changes in the column/field in the main file if will not affect.

Even I added a new column, I want to not affect the column number of the status and remarks field

=Arrayformula(IFERROR(VLOOKUP($A5:A,IMPORTRANGE("https://docs.google/spreadsheets/d/165iY-i_BYz5BWOX8ae6lBIh9gPJee0lnfmceCCtzpmM/edit?gid=0#gid=0","Sheet1!A3:H"),**ROW1 SELECTED IN THE SOURCE FILE**,0)))

SOURCE FILE: https://docs.google/spreadsheets/d/1x3Upa0qhTItyVjXiNwTaIsbajq4ac4cZsX7Pap27KGE/edit?usp=sharing

TARGET FILE: https://docs.google/spreadsheets/d/165iY-i_BYz5BWOX8ae6lBIh9gPJee0lnfmceCCtzpmM/edit?gid=0#gid=0

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 20, 2024 at 6:39 Rhenz Idol Ii San Pedro asked Nov 20, 2024 at 6:17 Rhenz Idol Ii San PedroRhenz Idol Ii San Pedro 511 silver badge22 bronze badges 7

• Your target file is not accessible. Please share it as public. – Harun24hr Commented Nov 20, 2024 at 6:21
• Hi @Harun24hr updated already. – Rhenz Idol Ii San Pedro Commented Nov 20, 2024 at 6:23
• Please edit the question and add the sample data (as text) inside it - A question should not depend on external resources in order to be solved – DarkBee Commented Nov 20, 2024 at 6:30
• 1 HELPFUL INFO: Sharing a google sheet and/or screenshots as the only source of test data is not encouraged on Stackoverflow. The recommended method is to provide a sample of test data as markdown table. You can use this tool to create the markdown_format & add it to your question. Here's one example question to get a picture on how its done. – rockinfreakshow Commented Nov 20, 2024 at 6:36
• 1 Did you also checkout the instructions comment on how to & NOT to share the test data here on Stack?! – rockinfreakshow Commented Nov 20, 2024 at 6:48

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

Try this:

Using the Match Function to Adjust CHOOSECOLS function. I am updating this answer for future readers. The Goal is to find a specific header and use it as a base for a filter.

=LET(x, IMPORTRANGE("Your Spreadsheet","SHEET1!A1:H"), BYROW(A2:A5, LAMBDA(r, FILTER(HSTACK(CHOOSECOLS(x,MATCH("STATUS",CHOOSEROWS(x,2),0)),CHOOSECOLS(x,MATCH("REMARKS",CHOOSEROWS(x,2),0))),CHOOSECOLS(x,1)=r))))

This formula is based on how long the range is going to be and then, assuming that the one you want to take would be the last column and second to the last column.

Sample output:

Reference:

Match

Filter

本文标签： google sheetsVLOOKUP IMPORTRANGE to select the column number in another file alsoStack Overflow