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.com/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.com/spreadsheets/d/1x3Upa0qhTItyVjXiNwTaIsbajq4ac4cZsX7Pap27KGE/edit?usp=sharing
TARGET FILE: https://docs.google.com/spreadsheets/d/165iY-i_BYz5BWOX8ae6lBIh9gPJee0lnfmceCCtzpmM/edit?gid=0#gid=0
Using the MATCH
function to adjust CHOOSECOLS
function 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, assuming you want the last and second to the last column: