VLOOKUP and INDIRECT, TEXT formula combination

user_16650051 0
Viewed 56

Here is perfectly working formula I have combines with the help of Alistair McEvoy and idfurw:

=HYPERLINK(VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/","08/16!A2:Q26"),6), VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/","08/16!A2:Q26"),9))

However, I want to update it. Especially this part: "08/16!A2:Q26" from above formula.

How do I re-write formula so that instead of 08/16 would be value from cell A1. Cell A1 contains 08/16 (date format).

This formula gets me data from different unopened google sheet. Unopened spreadsheet contains sheet named 08/16 where data should be extracted from.

I tried this combination but it did not work:

=HYPERLINK(VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",INDIRECT("'"&TEXT($A$1,"MM/dd")&"'!A2:Q26"),6), VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",INDIRECT("'"&TEXT($A$1,"MM/dd")&"'!A2:Q26"),9))

Probably I am doing mistakes with ' and "

So I want to transform "08/16!A2:Q26" into --> "08/16!A2:Q26" but instead of 08/16 will be value from A1 cell. A1 cell value is 08/16

vlookup google-query-language google-sheets-formula google-sheets string-formatting
1 Answers

try:

=HYPERLINK(
 VLOOKUP(A4, 
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 A1&"!A2:Q26"), 6), VLOOKUP(A4,
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 A1&"!A2:Q26"), 9))

or:

=HYPERLINK(
 VLOOKUP(A4, 
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 TO_TEXT(A1)&"!A2:Q26"), 6), VLOOKUP(A4,
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 TO_TEXT(A1)&"!A2:Q26"), 9))
user_56326290
Related