Search code examples
google-sheets-formula

I need an ArrayFormula to return images in cells but in a reverse Vlookup format

I am stuck on obtaining an array of images in cells from another sheet based on the criteria to the right. This is because QUERY cannot return image values, and INDEX MATCH cannot be an array... Is there a solution? LINK TO SHEET.

The reason I would like it to be an array is because I will be tying this formula into another formula calculated by QUERY and I will end up just putting a clause in that says If in column x use this formula, otherwise use query.

Sheet 2 (hardcoded data)

Data Sheet

Sheet 1 (formula needed in A1)

enter image description here

Solution

  • Assuming names to match images to are in range B1:B, and a list of images and corresponding names in Sheet2!A1:B then the following formula should work:

    =ARRAYFORMULA(IF(ISBLANK(B1:B),,VLOOKUP(B1:B,{Sheet2!B$1:B,Sheet2!A$1:A},2,0)))

    I have also added it to your sample in a new sheet to demonstrate that it works.

    Explanation

    First, VLOOOKUP is a single formula that does what you tried do do with INDEX(MATCH), that is returns the corresponding value from another column in the row where the match is found. It always searches the first column, so in this case I used an array {Sheet2!B$1:B,Sheet2!A$1:A}, where the names are in the first column and images in the second.

    The $ before the row numbers in the range to search by VLOOKUP, e.g. Sheet2!B$1:B instead of Sheet2!B1:B are crucial, so that these ranges will not get modified for each consecutive row that ARRAYFORMULA uses (like Sheet2!B2:B in row 2, Sheet2!B3:B in row 3, and so on) and VLOOKUP will always search in the whole range starting from cell Sheet2!B1.