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

• 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

1 Answer 1

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