Return Multiple values by vlookup and importrange

Question

I have two worksheets. First : https://docs.google.com/spreadsheets/d/1tkJlP0OYk41J87lzjLmbKbSYDp79TcROtp56LdwSghk/edit?usp=sharing, second: https://docs.google.com/spreadsheets/d/1s-xdbfz_HgxEp-jqqaTpTUFvrbxXo2VvAnjSYZ59lPk/edit?usp=sharing

I will work on the second worksheet. I want to return multiple values by array formula particularly for Data 5 and Data 6 only which I get from the first worksheet. How ever, I will manually input some values in "Call cat 1" and "Call ID". The returned values of data 5 and data 6 should be the same according to the first worksheet.

I tried to put vlookup, query, and importrange altogether but the formula seems to be contradicting each other.

Would anyone help me out? Thanks

player0 player0 · Accepted Answer · 2020-08-10T06:00:12

try:

=ARRAYFORMULA(IF(A2:A<>"JU",,IFNA(VLOOKUP(B2:B, QUERY(IMPORTRANGE(
 "1tkJlP0OYk41J87lzjLmbKbSYDp79TcROtp56LdwSghk", "Sheet1!A2:H"), 
 "select Col2,Col7,Col8"), {2, 3}, 0))))

or maybe you want:

=ARRAYFORMULA(IF(A2:A="",,(VLOOKUP(A2:A&B2:B, {IMPORTRANGE(
 "1tkJlP0OYk41J87lzjLmbKbSYDp79TcROtp56LdwSghk", "Sheet1!A2:A")&IMPORTRANGE(
 "1tkJlP0OYk41J87lzjLmbKbSYDp79TcROtp56LdwSghk", "Sheet1!B2:B"), IMPORTRANGE(
 "1tkJlP0OYk41J87lzjLmbKbSYDp79TcROtp56LdwSghk", "Sheet1!G2:H")}, {2, 3}, 0))))

Return Multiple values by vlookup and importrange

1 Answers