VLOOKUP IMPORTRANGE to select the column number in another file also
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:
- google spreadsheet query function column id other than letter of column
- How do I generate OAuth ODBC keys?
- Copy data from one sheet to another, overwriting already entered data
- Autocomplete MaterializeCSS is not working
- If date field is blank return blank but if it has a date return the day in week
- Search for Value and Return All Columns In Which Value Appears
- How to pass a range as a function parameter?
- Google Script pulling cached data
- Select column minus header (or deselect cell)
- How to split array of strings by a delimiter with more than 1 characters
- In sheet one, pull sheet two's cell values by using sheet one's cell values as variables?
- Match Value based on corresponding dates
- Formula to Display Each Multi-Select Value in Google Sheets Dropdown on a Separate Line
- I would like to always have the row above open for new data
- Trouble with Google Apps Script returning a string instead of an integer in Google Sheets
- How to stack multiple queries indirectly defined in an array
- Repeat a function for all selected cells in a range
- Apps Script: eval(include("FilterUICode")) fails with SyntaxError: expected expression, got '<' when injecting JS from separate file
- Google sheets setting columns range in app script
- Reset Column and Row sizes to default
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Displaying hyperlinks to other sheet tabs in a custom sidebar
- Google apps scripts button: can I assign a "global" script that's not attached to the worksheet?
- Date format showing incorrectly in Google sheet from Big Query API
- Formula returns blank while showing result in tooltip
- Deleting all columns if a cell is blank
- Data validation for time input in Google Sheets
- How to hide certain columns?
- How to hide columns across multiple sheets?
- Hide an array of columns in Google App Scripts