Any way to use a VLOOKUP formula in Numbers to insert an image instead of text value

numbers

The intent is to create a sheet that references orders from customers with an image of each item ordered from my catalog of inventory.

I need to create a spreadsheet that references item SKUs to display an image of a unique item. For example: 004 entered in cell A:1 should lookup 004 on sheet "images" and return the value in column 2 that matches 004. I have successfully created this formula using the VLOOKUP function.

However, I want Numbers to populate the image in the resulting cell. The way my formula currently works it will only return the text in the cell. How do I get it to return the image value? If I delete the text out of the cell corresponding to 004 the result returned is 0 even though there is an image pasted into the cell.

Current formula:

VLOOKUP($A2,images::Table 1::A$1:B$10,2,FALSE)

I had dragged images from the Finder into cells but it appears as though they only show up as background images in the cells or something like that.

Best Answer

The images aren't the actual content of the cell, they're just cell formatting (background image), and formula cannot interact with formatting, only with cell contents.
Conditional formatting can only apply a restricted set of formatting options and unfortunately images are not one of these.
AppleScript does not provide the ability to set cell background images, only cell background colours (even though shapes and text items do have the ability for background images).

Therefore currently this is not possible.

Related Solutions

Style text in Numbers formula

Although one cannot style individual characters in a formula, one feature of Apple Numbers is the canvas. One can take advantage of this by adding tables and text objects anywhere.

The above screen shot uses a static (as the OP's "Invoice terms" text is hard coded in their formula example, it is assumed static) text field placed next to the table (a single cell table with no border, in this case) containing the cell with the formula.

The OP's use of the CONCATENATE function is unneeded as the ampersands do the same job. To use CONCATENATE in this case:

=CONCATENATE(Terms,", please pay by...")

Where Terms is the reference to the cell containing the Terms text.

Detailed screenshot:

By using a number for the days, this can be used in a calculation against the Invoice date to determine the date payment is due. But that is a topic for another question. ;)

Auto-Populate A Pop-Up Menu in Numbers 3.5

I had the same sort of problem and with some help from people within the Apple Support community I came up with this solution. Hopefully it will work for you.

This is my list of players in sheet 1. In your case, the Players name would be the item codes.

Player list

Here is sheet where I want to information copied to. Sheet 2

The P# corresponds to the number next to the Players name in Sheet 1. If I enter a 1 and a 2 the names are filled in to the column to the left of the box. enter image description here

This shows the formula required to transfer the players name from sheet 1 to sheet 2. enter image description here

To take it a step further, when I fill in the Players winnings in Sheet 1 it also fills it in all the "Earnings" cells for that player in Sheet 2 using the formula shown below. enter image description here

enter image description here

My list of players goes to 300 but the last 20 or so are empty. That way I can still add more names or in your case, item codes, to it.

This may be more complicated than you require but hopefully it helps you.

Best Answer

Related Solutions

Style text in Numbers formula

Auto-Populate A Pop-Up Menu in Numbers 3.5

Related Question