Google Sheets: IMPORTRANGE Values Matched To A Custom ID

enels 0
Viewed 24

I am building a Google sheet that references data from a source sheet where columns and rows may shift, and values may even change. Because shifting can occur, an ID system has been implemented. Here is a very simplified version:

Example Source Sheet

In my own sheet, the goal is to pull in values and place them based on the corresponding ID. Currently, to pull this off, I am using the IMPORTRANGE function with a FILTER to get the desired value, based on the ID in the column. So to find and place the value "United States" underneath ID 1, I use the following:

=FILTER(IMPORTRANGE("doc_url", "Sheet1!A1:A5"),IMPORTRANGE("doc_url", "Sheet1!B1:B5")=A1)

Example My Own Sheet

I am aware it's not super great to use IMPORTRANGE on a cell-by-cell basis, as it becomes quite intensive with heavy usage (and the actual sheet I am building has many more values to pull). I feel like there must be a more efficient way to pull and arrange these values by the correct ID, but I haven't been able to figure it out so far.

Is it possible to build a function that would pull the full range in the order I am expecting all in one go?

google-sheets
1 Answers

try in A2:

=INDEX(IFNA(HLOOKUP(A1:E1, 
 TRANSPOSE(QUERY(IMPORTRANGE("doc_url", "Sheet1!A1:B5"), 
 "select Col2,Col1", )), 2, 0)))

enter image description here

player00
Related