Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH Functions

Welcome to excel metric number 882. If you want to download this workbook, XML Shrek, 882, 2007, lookup picture index, click on the link below the video Hey in the last video, eight, eight, one, we did look up a picture. Now I was using 2010, and it worked perfectly there. We in essence, put a picture here and then put a picture inside I'm, sorry, a formula inside the picture, and it allowed us to do a lookup. Now in 2007, using a picture, doesn't work and there are lots of comments below the video saying, I, can't, get. It to work and Joe said, no problem, instead of using a picture use a bitmap.

Alright. So let's see how to do this. First thing is I. Do want to you have your database. Your lookup column, I have prices and pictures here, I'm going to insert a picture here use the keyboard, shortcut, alt, NP and I find my picture and double click and insert it and fit the picture in the cell.

Alright, you can use your arrow keys to move it around wherever you want. Alright. Now in the 8/8 will in the last video I, copied this.

Picture and pasted it right here, and then try to put a formula, but you can't I, can't, click in the formula bar, right? So no problem before we insert our bit mat and then see how to do it that way let's do our formula. Now, a few people or someone in a day one and a comment said, hey, how come you can't use lookup to look up a picture? I, don't know, I tried and that didn't work, so we're going to switch to index and match because that'll work, actually before that let's do data validation. And our little.

Formula and then we'll do lookup picture. So in this cell I'm going to do alt DL, this is data validation, I'm going to hit tab in to allow Ito 'm going to hit L to get to list tab and then enter. So now we have our lookup.

We can use lookup to look up the price right, we're going to look up that comma within our little database. Comma. Second column has the price comma, and then we're doing an exact match close parentheses 24. All right.

So now we need to do the formula for before. We do our bit mat and. Insert the formula let's make the formula in the cell for looking at the picture index is a lookup function. It'll be able to do this I'm going to click in cell d5 left arrow.

And then shift up hit f4 to lock it comma, the row number we're going to use the match function. Max function to look up a value and tell you the position, so I'm going to click there and f4. We need to lock all of these in this formula. So when we put it up into our define name, it works all right. The lookup arrays.

Notice we're. Looking up this, and the match will look through here and tell us the position, which will correspond to the picture f4 comma exact match. So we put 0 close parentheses.

That is the row number right? There, close parenthesis now, I'm going to copy this control f3 to open up name manager, new I'm going to call it boom, kick down here, control V and then click OK there. It is right there, there's, our name click close now, let's insert our bitmap object. So insert text group object, bitmap, okay, mine's going to. Open up paint I'll have to close that I'm going to size this. You can actually hold the Alt key and get it to sit right in a corner nicely, I actually want to move it around.

You can size it. However, you want them using the arrow keys to move it around. And now look at that, when you insert that bitmap, you get this little formula, I'm going to highlight it type equals B, Oh tab and enter. And so now that bitmap that great trick from Joe. Now, when I select been, we get the price and the picture select.

Deuce, we get the price and the picture. Alright, see you next trip.

Dated : 18-Apr-2022

Leave Your Comment