How to do a reverse lookup in Excel Video

Get it off your chest – be the first to comment on this video!

How to do a Reverse Lookup in Excel

Bill Jelen: Hey, I am Bill Jelen from mrexcel.com and I’ve got a cool excel tip for you today. Mike Gel Girvin: Hey! This is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that. Bill Jelen: Holy cow! Here we are, episode #1000 in the Mr. Excel net cast. I want to thank you for stopping by. I want to say a shout out to my friends who were in Toronto, Amber McArthur—Leo Laporte, they were the ones who several years ago were the first people I knew in podcast and said, “Hey, you should do a Mr. excel podcast.” And here we are and I’d never thought I’d hit episode #1000. Now, I’m interested that many weird happened today, do we have any P1k problems, like the Y2K problems. The episode show up a bit wrong side of iTunes or whichever pod catcher you are using, it’s sort incorrectly right now I got—If you have any weirdness, send me on print screen, bill@mrexcel.com just so we can see what is going on all right. Dueling podcast Mike, he said let’s do a reverse look up; reverse look up so we have this schedule here and I want to figure out where for example Truck 1 is and it might occur in more than one place. Now, Mike is going to do this with a formula. That formula makes my head spin, I have to tall you. I’m just going to use VBA, the fastest way to go, ten twelve, lots of code, we’re going to have a function that will do this for us. So, I’m going to come here to visual basic, insert a new module and I’m going to create a function, not a cell—function. I’m going to call it RL for reverse look up; two parameters, we’re going to pass the value so that’s called TruckValue as Range and then also where we’re going to look so the LookupTable Range, here we go. Okay so, when I think about this, they are going to specify, they are going to look for Truck 1 within this Range here. So I want to grab the heading row and the heading column because that is what I need to report back—the heading row and heading column so I want to clear out that the head row; HRow = LookupTable.Rows(1).Row – 1. And then the heading column, HCol = LookupTable e.Columns(1).Colum—so I know where to look. Now, the trick with the function is that you have to have a variable that is the same as the function names, that going to be RL. Seek—I’m going to initial as that to nothing. We start out with nothing and I say for each cell—cell is another variable and LookupTable if cell.value = TruckValue.Value then I’m going to do something and End if. Next cell. All right, so now let’s think about this, the part. I’m going to do something. That variable RL = RL & and then we’re going to add in cells—which row do I want? Well I want the row—let’s take a look at the data. I want to get the date first so it’ always me on row number 1 and which column do I want? I want cell.column.value & “ “space, another & and now we need to grab the time from column A so that is going to go be cell.row and hcol.value and then I am going to join that with a semicolon and a space, that way we always have a semicolon between a various variables. RL = RL & Cells(1, cell.column).value & “ “ & Cells(cell.Row, HCol).value & “; “ Now back here, where I heard quoted one that wasn’t very good. It actually said HRow where we had a variable for that okay so. RL = RL & Cells(HRow, cell.column).value & “ “ & Cells(cell.Row, HCol).value & “; “ There we go, let’s see, one, two, three, four, five six, seven, eight lines of code. I think it’s going to solve this, let us do a quick test, will come back here and were going to say; =RL for reverse lookup. Going lookup Truck number 1 within this Range here, I’m going to make that be absolute, so I press F4, closing parenthesis and that Truck is scheduled two places, 11/19 at 8:00 am, there it is and 11/21 at 9:00 am, there it is, perfect. Let’s copy this down and you see it works perfectly. So quick little custom function switch over to VBA, do a user defined function, we’re good to go. All right, let’s throw this over to Mike. Let’s see what Mike comes up with. Mike Gel Girvin: Hey thanks Mr. Excel. Hey but were going to go back to last week podcast 995. You gave me the point, but really, I got that trick from the Mr. Excel message board so the message board gets the point. Not only that but this week, I posted multiple times over the last year to the message board about Reverse Lookup and got help from people like Aladdin and Dominique. All right, Row Header or Column here, I’m going to have to do two different formulas and then concatenate them together over here to get a time and a date. I have the Truck list here and I’ve done a COUNTIF—counting how many Trucks there are here which will help us in our formula. Let’s do out Row Header formula first. Now we’ll start of with =IF and we need some way to run the index formula to look up the header and footer when we have looked up less than two but once we exceed and get to three we need a blank. So we’re going to do a three true, false. We use a Columns with an s and we’ll say Columns from this cell right here, downside in front of the $B8:B8—that will increment a number as we copy it this way and that will be less than or equal to this. Again, lock it in front of the column reference, that way, this way, we get the two and later we get the when we go down, we’ll get the next row information. IF=(COLUMNS($B8:B8)<=$G8 If that is true, then we’re going to use index to look at the time. Here is the time. F4, comma and now our Row, now—this is where it starts a little bit tricky. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5), Well row, we need—one, two, three or four depending on what Truck we see here. So we’ll use Match—Match and then we’re going to lookup the—lookup value is going to be this Truck Value right here and lock it in front of the column, comma, lookup array IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8, Now, we have duplicates—there is a 9 there and a 9 there, a one there and one there. So, we can’t just lookup the whole value. We’re going to actually have to extract from the whole table of Trucks, Columns, one at a time to match with the gate as the Column Header. So, once it’s the index function again is great for extracting columns of data. So we use index, the array is going to be the entire Range here, F4, comma IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5, And the trick for a extracting just a Column of numbers from a larger table is to put a zero right here. Comma later will do it or you could easily do it for column but put a zero there and will it extract the row. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0, So, if you have a zero for the row, now we need to get that Column and we need either a one, two or three. We’re going to use the small function—small function and inside of that, we’re going to use an IF because we have to ask hey—all of these right here, F4 are the any of those equal to our Truck, locking in front of the column. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8, If that comes out True, then we want to Column number for us. So, we’re going to do, Column and here are the Columns but this will be two, three, four. So from that, I have to subtract another Column. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1) Right now, we‘d see B1 minus B1 which would be 2 minus 2 as zero so we add one and the reason we this strange construct here is, this if it is copied and pasted anywhere, this little construct will always give us the right column. IF=COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1 Now, we’re in the middle of this IF, in the middle of SMALL, all we need is the Truth for the IF, we don’t need the False or close parentheses. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1) Right now, this will give us an array, if I highlight all these of columns; IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1) I hit F9; IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL({1,FALSE,FALSE;FALSE,FALSE,3;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE} One and three because there is a Truck there and a Truck there. Well, when we are dealing with this date here, we need the one but when we get over to here, at this date, we need three. I’m going to ctrl-z. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), That is why we’re using the SMALL and here is how we use—get it as we copy across the column, we use the same incrementing number ctrl-c, ctrl-v. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8) We close parenthesis on the small, these screen tips really come in handy when you have a huge formula like this. We’ve got out column number, remember this index is just to get a column of data for the Match so I close parenthesis. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))) That index right here if you highlight it and hit F9, look at that—it did. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,{“Truck1”; “Truck8”;”Truck 9”; “Truck6”} It’s good. So there is a Match for Truck and we’re with this state. We got that column of values—ctrl z to undo it. So we’ve completed extracting a column of numbers from Match, comma and the Match type since back here we’re looking up a Truck, we put zero, close parenthesis. Now, we have just got out row number from our—for our match right there, we do not need the columns so I‘ll close parenthesis. That whole thing right there is our index which is retrieving a particular time. We can see the screen tips says IF—we just did our value so—the value of true, comma and then the value of false is going to be our double quote, that show a blank when there is not a Match, close parenthesis. IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””) This is an array formula so were going to hold ctrl and shift and enter, copy it over and then down. Then we should get for Truck9 here at 10AM and an 8AM—10AM and an 8AM. Now, this studio is already running very long so I have a similar formula for the column, it’s a little bit easier, it’s not easy in the Match but that formula we have for the column header. Now, we need to grab both of these formulas and can concatenate them over here. Now watch this, I’m going to highlight this, scoop this out right her and ctrl-c and I’m going to show the clip board. In 2007, that is how you do it, in earlier version, you ctrl-c c. Notice it’s there, escape and then I come right here and scoop this out, ctrl-c, it’s loaded it up, there is a column in there, there is the row, escape, come on over here. There is going to be a problem if we concatenate this two formulas because this is format right here and formulas don’t see format so we’re going to—right here to equals text and then will just paste. This is our Row Header, our time. So inside of the text, which will convert a number and format and create text, so we’ll put comma and in double quote, we’ll put hour, hour, colon, minute, minute, space, a/p and double quote, close parentheses. =TEXT(IF=(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””) Now, we also are going to need to concatenate this so I’m going to ampersand and instead of just ampersand it, I’m going to use character 10, we’ll see it would give me a hard return, so the date will jump down to the next line and then another text. Will paste this big, huge formula right here, comma and then we use in double quote, the custom number format, for months/days/year and double quote, close parenthesis, I got my finger crosses, ctrl shift enter. =TEXT(IF=(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””),””),””hh:mm A/P) Now, we do need Word Wrap. Put it right there just because the hard return that character will send it down to the next line but we need Word Wrap to show it in the cells and double click and send it down, highlight all these rows and let’s see, do we get down here for Truck9, we have 10:00 am on the 19th, 8:00 am on the 21st. =TEXT(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””),””),””hh:mm A/P)&(10)&TEXT(IF=(COLUMNS($18:18)<=$G8,INDEX($B2:$D1),,SMALL(IF($B$2:$D$5=$H8,COLUMN(&18:18))),””),”m/d/y”) All right, that is the point for Mr. Excel because that VBA—much faster than that gigantic ridiculous formula. All right, see you next trick. Bill Jelen: Hey Mike, that was really, really cool. Now, I’m going to try to ever have a reverse lookup. A couple of things I learned in watching your tip, first of all, I never realized that you can put a zero as one of the arguments in index and have it retuned an entire array, either a column or a row, very, very cool. Also, I loved your trick with a clipboard; showing that clipboards that way, you could paste one piece of the formula into the text function and then the other piece of the formula into the text function, what a great trick right there at the little dialog actually at the bottom of the clipboard group. I love that one. Hey, thanks Mike and thanks to you for watching and we’ll see you next for another net cast from Mr. Excel and Excel is Fun.

No Links were listed yet. Go ahead and share!

Bill Jelen: Hey, I am Bill Jelen from mrexcel.com and I’ve got a cool excel tip for you today.

Mike Gel Girvin: Hey! This is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.

Bill Jelen: Holy cow! Here we are, episode #1000 in the Mr. Excel net cast. I want to thank you for stopping by. I want to say a shout out to my friends who were in Toronto, Amber McArthur—Leo Laporte, they were the ones who several years ago were the first people I knew in podcast and said, “Hey, you should do a Mr. excel podcast.” And here we are and I’d never thought I’d hit episode #1000.

Now, I’m interested that many weird happened today, do we have any P1k problems, like the Y2K problems. The episode show up a bit wrong side of iTunes or whichever pod catcher you are using, it’s sort incorrectly right now I got—If you have any weirdness, send me on print screen, bill@mrexcel.com just so we can see what is going on all right.

Dueling podcast Mike, he said let’s do a reverse look up; reverse look up so we have this schedule here and I want to figure out where for example Truck 1 is and it might occur in more than one place.

Now, Mike is going to do this with a formula. That formula makes my head spin, I have to tall you. I’m just going to use VBA, the fastest way to go, ten twelve, lots of code, we’re going to have a function that will do this for us.

So, I’m going to come here to visual basic, insert a new module and I’m going to create a function, not a cell—function. I’m going to call it RL for reverse look up; two parameters, we’re going to pass the value so that’s called TruckValue as Range and then also where we’re going to look so the LookupTable Range, here we go.

Okay so, when I think about this, they are going to specify, they are going to look for Truck 1 within this Range here. So I want to grab the heading row and the heading column because that is what I need to report back—the heading row and heading column so I want to clear out that the head row; HRow = LookupTable.Rows(1).Row – 1. And then the heading column, HCol = LookupTable e.Columns(1).Colum—so I know where to look.

Now, the trick with the function is that you have to have a variable that is the same as the function names, that going to be RL. Seek—I’m going to initial as that to nothing. We start out with nothing and I say for each cell—cell is another variable and LookupTable if cell.value = TruckValue.Value then I’m going to do something and End if. Next cell.

All right, so now let’s think about this, the part. I’m going to do something. That variable RL = RL & and then we’re going to add in cells—which row do I want? Well I want the row—let’s take a look at the data. I want to get the date first so it’ always me on row number 1 and which column do I want? I want cell.column.value & “ “space, another & and now we need to grab the time from column A so that is going to go be cell.row and hcol.value and then I am going to join that with a semicolon and a space, that way we always have a semicolon between a various variables.

RL = RL & Cells(1, cell.column).value & “ “ & Cells(cell.Row, HCol).value & “; “

Now back here, where I heard quoted one that wasn’t very good. It actually said HRow where we had a variable for that okay so.

RL = RL & Cells(HRow, cell.column).value & “ “ & Cells(cell.Row, HCol).value & “; “

There we go, let’s see, one, two, three, four, five six, seven, eight lines of code. I think it’s going to solve this, let us do a quick test, will come back here and were going to say;
=RL for reverse lookup. Going lookup Truck number 1 within this Range here, I’m going to make that be absolute, so I press F4, closing parenthesis and that Truck is scheduled two places, 11/19 at 8:00 am, there it is and 11/21 at 9:00 am, there it is, perfect.

Let’s copy this down and you see it works perfectly. So quick little custom function switch over to VBA, do a user defined function, we’re good to go.

All right, let’s throw this over to Mike. Let’s see what Mike comes up with.

Mike Gel Girvin: Hey thanks Mr. Excel. Hey but were going to go back to last week podcast 995. You gave me the point, but really, I got that trick from the Mr. Excel message board so the message board gets the point. Not only that but this week, I posted multiple times over the last year to the message board about Reverse Lookup and got help from people like Aladdin and Dominique.

All right, Row Header or Column here, I’m going to have to do two different formulas and then concatenate them together over here to get a time and a date. I have the Truck list here and I’ve done a COUNTIF—counting how many Trucks there are here which will help us in our formula.

Let’s do out Row Header formula first. Now we’ll start of with =IF and we need some way to run the index formula to look up the header and footer when we have looked up less than two but once we exceed and get to three we need a blank. So we’re going to do a three true, false. We use a Columns with an s and we’ll say Columns from this cell right here, downside in front of the $B8:B8—that will increment a number as we copy it this way and that will be less than or equal to this. Again, lock it in front of the column reference, that way, this way, we get the two and later we get the when we go down, we’ll get the next row information.

IF=(COLUMNS($B8:B8)<=$G8

If that is true, then we’re going to use index to look at the time. Here is the time. F4, comma and now our Row, now—this is where it starts a little bit tricky.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),

Well row, we need—one, two, three or four depending on what Truck we see here. So we’ll use Match—Match and then we’re going to lookup the—lookup value is going to be this Truck Value right here and lock it in front of the column, comma, lookup array

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,

Now, we have duplicates—there is a 9 there and a 9 there, a one there and one there. So, we can’t just lookup the whole value. We’re going to actually have to extract from the whole table of Trucks, Columns, one at a time to match with the gate as the Column Header.

So, once it’s the index function again is great for extracting columns of data. So we use index, the array is going to be the entire Range here, F4, comma

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,

And the trick for a extracting just a Column of numbers from a larger table is to put a zero right here. Comma later will do it or you could easily do it for column but put a zero there and will it extract the row.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,

So, if you have a zero for the row, now we need to get that Column and we need either a one, two or three.

We’re going to use the small function—small function and inside of that, we’re going to use an IF because we have to ask hey—all of these right here, F4 are the any of those equal to our Truck, locking in front of the column.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,

If that comes out True, then we want to Column number for us. So, we’re going to do, Column and here are the Columns but this will be two, three, four. So from that, I have to subtract another Column.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)

Right now, we‘d see B1 minus B1 which would be 2 minus 2 as zero so we add one and the reason we this strange construct here is, this if it is copied and pasted anywhere, this little construct will always give us the right column.

IF=COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1

Now, we’re in the middle of this IF, in the middle of SMALL, all we need is the Truth for the IF, we don’t need the False or close parentheses.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1)

Right now, this will give us an array, if I highlight all these of columns;

IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1)

I hit F9;

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL({1,FALSE,FALSE;FALSE,FALSE,3;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}

One and three because there is a Truck there and a Truck there. Well, when we are dealing with this date here, we need the one but when we get over to here, at this date, we need three. I’m going to ctrl-z.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1),

That is why we’re using the SMALL and here is how we use—get it as we copy across the column, we use the same incrementing number ctrl-c, ctrl-v.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8)

We close parenthesis on the small, these screen tips really come in handy when you have a huge formula like this. We’ve got out column number, remember this index is just to get a column of data for the Match so I close parenthesis.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8)))

That index right here if you highlight it and hit F9, look at that—it did.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,{“Truck1”; “Truck8”;”Truck 9”; “Truck6”}

It’s good. So there is a Match for Truck and we’re with this state. We got that column of values—ctrl z to undo it. So we’ve completed extracting a column of numbers from Match, comma and the Match type since back here we’re looking up a Truck, we put zero, close parenthesis.

Now, we have just got out row number from our—for our match right there, we do not need the columns so I‘ll close parenthesis. That whole thing right there is our index which is retrieving a particular time. We can see the screen tips says IF—we just did our value so—the value of true, comma and then the value of false is going to be our double quote, that show a blank when there is not a Match, close parenthesis.

IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””)

This is an array formula so were going to hold ctrl and shift and enter, copy it over and then down.

Then we should get for Truck9 here at 10AM and an 8AM—10AM and an 8AM. Now, this studio is already running very long so I have a similar formula for the column, it’s a little bit easier, it’s not easy in the Match but that formula we have for the column header.

Now, we need to grab both of these formulas and can concatenate them over here. Now watch this, I’m going to highlight this, scoop this out right her and ctrl-c and I’m going to show the clip board. In 2007, that is how you do it, in earlier version, you ctrl-c c. Notice it’s there, escape and then I come right here and scoop this out, ctrl-c, it’s loaded it up, there is a column in there, there is the row, escape, come on over here. There is going to be a problem if we concatenate this two formulas because this is format right here and formulas don’t see format so we’re going to—right here to equals text and then will just paste. This is our Row Header, our time. So inside of the text, which will convert a number and format and create text, so we’ll put comma and in double quote, we’ll put hour, hour, colon, minute, minute, space, a/p and double quote, close parentheses.

=TEXT(IF=(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””)

Now, we also are going to need to concatenate this so I’m going to ampersand and instead of just ampersand it, I’m going to use character 10, we’ll see it would give me a hard return, so the date will jump down to the next line and then another text. Will paste this big, huge formula right here, comma and then we use in double quote, the custom number format, for months/days/year and double quote, close parenthesis, I got my finger crosses, ctrl shift enter.

=TEXT(IF=(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””),””),””hh:mm A/P)

Now, we do need Word Wrap. Put it right there just because the hard return that character will send it down to the next line but we need Word Wrap to show it in the cells and double click and send it down, highlight all these rows and let’s see, do we get down here for Truck9, we have 10:00 am on the 19th, 8:00 am on the 21st.

=TEXT(IF=(COLUMNS($B8:B8)<=$G8,INDEX($A$2:$A$5),MATCH($H8,INDEX($B$2:$B$5,0,SMALL(IF($B$2:$B$5=$H8,COLUMN($B$1:$D$1)-COLUMN($B$1)+1), =(COLUMNS($B8:B8))),0)),””),””),””hh:mm A/P)&(10)&TEXT(IF=(COLUMNS($18:18)<=$G8,INDEX($B2:$D1),,SMALL(IF($B$2:$D$5=$H8,COLUMN(&18:18))),””),”m/d/y”)

All right, that is the point for Mr. Excel because that VBA—much faster than that gigantic ridiculous formula. All right, see you next trick.

Bill Jelen: Hey Mike, that was really, really cool. Now, I’m going to try to ever have a reverse lookup. A couple of things I learned in watching your tip, first of all, I never realized that you can put a zero as one of the arguments in index and have it retuned an entire array, either a column or a row, very, very cool.

Also, I loved your trick with a clipboard; showing that clipboards that way, you could paste one piece of the formula into the text function and then the other piece of the formula into the text function, what a great trick right there at the little dialog actually at the bottom of the clipboard group. I love that one.

Hey, thanks Mike and thanks to you for watching and we’ll see you next for another net cast from Mr. Excel and Excel is Fun.

Transcription by: Scribe4you Transcription Services

How to do a Reverse Lookup in Excel

How to do a reverse lookup in Excel -

How to do a reverse lookup in Excel -