Bill and Mike take a look at how to do a reverse lookup in Episode #1000.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Holy cow! Here we are, episode #1000 of the MrExcel netcast! I want to thank you for stopping by, want to send a shout-out to my friends who were in Toronto, Amber McArthur, Mike Lazera(?), Leo Laporte. They were the ones that several years ago were the first people I knew in podcasting, and said “Hey, you should do a MrExcel podcast.” And here we are, and never thought I’d hit episode #1000. Now I’m interested if anything(?) weird happened today, do we have any P1K problems, like the Y2K problems, did the episode show up at the wrong side of iTunes, or whichever podcatcher you’re using to sort incorrectly, or anything like that. If you have any weirdness, send me a printscreen, Bill@mrexcel.com, just so we can see what’s going on.
Alright, Dueling podcast, Mike said “Let’s do a reverse lookup!” So we have this schedule here, and I want to figure out where, for example Truck1 is, and it might occur in more than one place. Now, Mike’s going to do this with a formula, and that formula makes my head spin, I have to tell you, I’m just going to flat-out use VBA. Fastest way to go, 10-12 lines 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 sub, I’m going to call it RL, Reverse Lookup. Two parameters we’re going to pass, the value, let’s call it TruckValue as Range, and then also what we’re going to look, the LookupTable as Range, and here we go, OK.
So, when I think about this, they are going to specify, they want to look for Truck1 within this range here. So I want to grab the heading row and the heading column, because that’s what I need to report back, the heading row and the heading column. So I want to figure out the head row, HRow = LookupTable.Rows(1).Row – 1. And then the heading column, HCol = LookupTable.Columns(1).Column – 1, that way 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 name, so that’s going to be RL = , I’m going to initialize that to nothing, we start out with nothing. And then I say For Each cell (cell is another variable) in LookupTable, If cell.Value = TruckValue.Value Then (I’m going to do something), End If, Next cell, alright.
So now, let’s think about this, the part where I’m going to do something. That variable, RL is going to be equal to the old value, &, 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’s always going to be row #1, and which column do I want?
I want cell.Column).Value & “ “ & and now I need to grab the time from column A, so that’s going to be Cells(cell.Row, Hcol).Value, and then I’m going to join that with a ; and a space, that way we’ll always have a ; between over those(?) values. Back here where I hardcoded a 1, that wasn’t very good of me, I should’ve said HRow, we already have a variable for that, OK. So, there we go, let’s see, 8 lines of code, I think it’s going to solve this.
Let’s do a quick test, we’ll come back here, and we’re going to say =RL for Reverse Lookup, we’ll lookup Truck1 within this range here. I’m going to make that B absolute so I’ll press F4, closing parenthesis, and that truck is scheduled in two places, 11/19 at 8AM, there it is, and 11/21 at 9AM, there it is, perfect, let’s copy this down, and you see that it works perfectly! So, quick little custom function, switch over to VBA to a user-defined function, we’re good to go, alright. Let’s throw this over to Mike, let’s see what Mike comes up with!
Mike: Hey, thanks MrExcel! Hey, but we got to go back to last week podcast 995, you gave me the point, but really, I got that trick from the MrExcel 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 a lot of people like Aladdin and Dominique. Alright, Row Header and Column Header, 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 have done a COUNTIF, counting how many trucks there are here, which will help us in our formula. Let’s do a Row Header formula first: Now I’m going to start off 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 2, but once we exceed and get to 3, we need a blank. So we’re going to do a TRUE/FALSE, we use COLUMNS (with an S), and we’ll say columns from this cell right here, $B8:B8, that’ll increment as we copy it this way, and that’ll be <= this.
Again, lock it in front of the column reference, that way, this way we get the 2 and later we get the Truck, but when you go down we get the next row information. If that’s TRUE then we’re going to use INDEX to look up the time, here’s the time, F4, comma, and now our row, that’s where it starts to get a little bit tricky.
Well row, we need 1 or 2 or 3 or 4, depending on what truck we see, so we’ll use MATCH, and we’re going to look up, the lookup_value is going to be this truck value right here, and lock it in front of the column, comma, lookup_array. Now we have duplicates, there’s a 9 there and a 9 there, a 1 there and a 1 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 the time, to match with the date as the Column Header. So watch this, the INDEX function again is great for extracting columns of data, so we’ll use INDEX. The array is going to be the entire range here, F4, comma, and the trick for extracting just a column of numbers from a larger table is to put a 0 right here, comma. Later we’ll do it- Or you could easily do it for column, we’ll put a 0 there and it’ll extract a row.
Now we need to get the column, and we need either a 1, 2, or 3. We’re going to use the 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 there any of those equal to our truck?” Lock it in front of the column. If that comes out TRUE, then we want a column number for us, so we’re going to do COLUMN, and here are the columns. But this would be 2-3-4, so from that I have to subtract another COLUMN. Right, now we see B1-B1 which would be 2-2=0, so we +1, and the reason we do this strange construct here is this, if it’s copied and pasted anywhere, this little construct will always give us the right column.
Now, we’re in the middle of this IF in the middle of a SMALL, all we need is the TRUE for the IF, we don’t need the FALSE, so ). Right, now this will give us an array, if I highlight all this, of columns, if I hit F9, 1 and 3, because there a truck there and a truck there. Well, when we’re dealing with this date here, we need the 1, but when we get over to here this date, we need the 3, I’m going to Ctrl+Z, that’s why we’re using the SMALL! And here’s how we get it as we copy it across the columns, we use the same incrementing number, Ctrl+C, Ctrl+V, we ) on the SMALL, these screen tips really come in handy when you have a huge formula like this. We got our column number, remember this INDEX is just to get a column of data for the MATCH, so I ). The INDEX right here, if you highlight it and hit F9, look at that, it did, since there was a MATCH for truck and we’re with this date, we get that column of values, Ctrl+Z to undo it.
So we’ve completed extracting a column of numbers for MATCH, comma, and the match type, since back here we’re looking up a truck, we put 0). Now we have just got our row number for our MATCH right here, we do not need the column, so I’ll ), that whole thing right there is our INDEX which is retrieving a particular time. We can see the screen tip says IF, we just did our values for TRUE, comma, and then the value of FALSE is going to be a “” that shows blank when there’s not a match, ). This is an array formula, so we’re going to hold Ctrl+Shift and Enter, copy it over, and then down, and we should get for truck 9 here a 10AM and an 8AM! Now, this video is already running very long, so I have a similar formula for the column, it’s a little bit easier, it’s not using the MATCH, but that formula we have for the header. Now we need to grab both of these formulas and concatenate them over here.
Now watch this, I’m going to highlight this, scoop this up right here and Ctrl+C, and I’m going to show the clipboard. 2007, that’s how you do it, in earlier versions you Ctrl+C C, notice it’s there, Esc, and then I come right here and scoop this out, Ctrl+C, it’s loaded up, there’s the column and there’s the row, Esc. Come on over here, there is going to be a problem if we concatenate these two formulas, this is format right here, and formulas don’t see format. So we’re going to, right here, do =TEXT, and then we’ll just paste, this is our Row Header, our time, so inside of the TEXT, which will convert a number and format it and create text. So we’ll put comma, and we’ll put “hh:mm A/P”). Now we also are going to need to concatenate this, so I’m going to &, instead of just & I’m going to use CHAR(10) will also give me a hard return, so the date will jump down to the next line, and then another TEXT. We’ll paste this big huge formula right here, comma, and then we use the custom number format “m/d/y”), I got my fingers crossed, Ctrl+Shift+Enter!
Now we do need Word Wrap, right there, just because the hard return character will send it down to the next line, but we need word wrap to show it in the cells. Double-click and send it down, highlight all these rows and let’s see, do we get down here, for Truck9 we have 10AM on the 19th, 8AM on the 21st, alright. That’s the point for MrExcel, because that VBA, MUCH faster than that gigantic ridiculous formula, alright, see you next trick!
Bill: Hey Mike, that was really, really cool!
Now, I’m not sure I ever have to do a reverse lookup, but a couple of things I’ve learned in watching your tip. First of all, I never realized you can put a 0 as one of the arguments in INDEX, and have it return an entire array, either a column or a row, very, very cool.
Also, I loved your trick with the clipboard!
Showing that clipboard, so that way you can 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, the little dialog launcher in the bottom of the Clipboard group, love that one!
Hey, thanks Mike, and thanks to you for watching, we’ll see you next time for another netcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Holy cow! Here we are, episode #1000 of the MrExcel netcast! I want to thank you for stopping by, want to send a shout-out to my friends who were in Toronto, Amber McArthur, Mike Lazera(?), Leo Laporte. They were the ones that several years ago were the first people I knew in podcasting, and said “Hey, you should do a MrExcel podcast.” And here we are, and never thought I’d hit episode #1000. Now I’m interested if anything(?) weird happened today, do we have any P1K problems, like the Y2K problems, did the episode show up at the wrong side of iTunes, or whichever podcatcher you’re using to sort incorrectly, or anything like that. If you have any weirdness, send me a printscreen, Bill@mrexcel.com, just so we can see what’s going on.
Alright, Dueling podcast, Mike said “Let’s do a reverse lookup!” So we have this schedule here, and I want to figure out where, for example Truck1 is, and it might occur in more than one place. Now, Mike’s going to do this with a formula, and that formula makes my head spin, I have to tell you, I’m just going to flat-out use VBA. Fastest way to go, 10-12 lines 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 sub, I’m going to call it RL, Reverse Lookup. Two parameters we’re going to pass, the value, let’s call it TruckValue as Range, and then also what we’re going to look, the LookupTable as Range, and here we go, OK.
So, when I think about this, they are going to specify, they want to look for Truck1 within this range here. So I want to grab the heading row and the heading column, because that’s what I need to report back, the heading row and the heading column. So I want to figure out the head row, HRow = LookupTable.Rows(1).Row – 1. And then the heading column, HCol = LookupTable.Columns(1).Column – 1, that way 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 name, so that’s going to be RL = , I’m going to initialize that to nothing, we start out with nothing. And then I say For Each cell (cell is another variable) in LookupTable, If cell.Value = TruckValue.Value Then (I’m going to do something), End If, Next cell, alright.
So now, let’s think about this, the part where I’m going to do something. That variable, RL is going to be equal to the old value, &, 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’s always going to be row #1, and which column do I want?
I want cell.Column).Value & “ “ & and now I need to grab the time from column A, so that’s going to be Cells(cell.Row, Hcol).Value, and then I’m going to join that with a ; and a space, that way we’ll always have a ; between over those(?) values. Back here where I hardcoded a 1, that wasn’t very good of me, I should’ve said HRow, we already have a variable for that, OK. So, there we go, let’s see, 8 lines of code, I think it’s going to solve this.
Let’s do a quick test, we’ll come back here, and we’re going to say =RL for Reverse Lookup, we’ll lookup Truck1 within this range here. I’m going to make that B absolute so I’ll press F4, closing parenthesis, and that truck is scheduled in two places, 11/19 at 8AM, there it is, and 11/21 at 9AM, there it is, perfect, let’s copy this down, and you see that it works perfectly! So, quick little custom function, switch over to VBA to a user-defined function, we’re good to go, alright. Let’s throw this over to Mike, let’s see what Mike comes up with!
Mike: Hey, thanks MrExcel! Hey, but we got to go back to last week podcast 995, you gave me the point, but really, I got that trick from the MrExcel 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 a lot of people like Aladdin and Dominique. Alright, Row Header and Column Header, 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 have done a COUNTIF, counting how many trucks there are here, which will help us in our formula. Let’s do a Row Header formula first: Now I’m going to start off 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 2, but once we exceed and get to 3, we need a blank. So we’re going to do a TRUE/FALSE, we use COLUMNS (with an S), and we’ll say columns from this cell right here, $B8:B8, that’ll increment as we copy it this way, and that’ll be <= this.
Again, lock it in front of the column reference, that way, this way we get the 2 and later we get the Truck, but when you go down we get the next row information. If that’s TRUE then we’re going to use INDEX to look up the time, here’s the time, F4, comma, and now our row, that’s where it starts to get a little bit tricky.
Well row, we need 1 or 2 or 3 or 4, depending on what truck we see, so we’ll use MATCH, and we’re going to look up, the lookup_value is going to be this truck value right here, and lock it in front of the column, comma, lookup_array. Now we have duplicates, there’s a 9 there and a 9 there, a 1 there and a 1 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 the time, to match with the date as the Column Header. So watch this, the INDEX function again is great for extracting columns of data, so we’ll use INDEX. The array is going to be the entire range here, F4, comma, and the trick for extracting just a column of numbers from a larger table is to put a 0 right here, comma. Later we’ll do it- Or you could easily do it for column, we’ll put a 0 there and it’ll extract a row.
Now we need to get the column, and we need either a 1, 2, or 3. We’re going to use the 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 there any of those equal to our truck?” Lock it in front of the column. If that comes out TRUE, then we want a column number for us, so we’re going to do COLUMN, and here are the columns. But this would be 2-3-4, so from that I have to subtract another COLUMN. Right, now we see B1-B1 which would be 2-2=0, so we +1, and the reason we do this strange construct here is this, if it’s copied and pasted anywhere, this little construct will always give us the right column.
Now, we’re in the middle of this IF in the middle of a SMALL, all we need is the TRUE for the IF, we don’t need the FALSE, so ). Right, now this will give us an array, if I highlight all this, of columns, if I hit F9, 1 and 3, because there a truck there and a truck there. Well, when we’re dealing with this date here, we need the 1, but when we get over to here this date, we need the 3, I’m going to Ctrl+Z, that’s why we’re using the SMALL! And here’s how we get it as we copy it across the columns, we use the same incrementing number, Ctrl+C, Ctrl+V, we ) on the SMALL, these screen tips really come in handy when you have a huge formula like this. We got our column number, remember this INDEX is just to get a column of data for the MATCH, so I ). The INDEX right here, if you highlight it and hit F9, look at that, it did, since there was a MATCH for truck and we’re with this date, we get that column of values, Ctrl+Z to undo it.
So we’ve completed extracting a column of numbers for MATCH, comma, and the match type, since back here we’re looking up a truck, we put 0). Now we have just got our row number for our MATCH right here, we do not need the column, so I’ll ), that whole thing right there is our INDEX which is retrieving a particular time. We can see the screen tip says IF, we just did our values for TRUE, comma, and then the value of FALSE is going to be a “” that shows blank when there’s not a match, ). This is an array formula, so we’re going to hold Ctrl+Shift and Enter, copy it over, and then down, and we should get for truck 9 here a 10AM and an 8AM! Now, this video is already running very long, so I have a similar formula for the column, it’s a little bit easier, it’s not using the MATCH, but that formula we have for the header. Now we need to grab both of these formulas and concatenate them over here.
Now watch this, I’m going to highlight this, scoop this up right here and Ctrl+C, and I’m going to show the clipboard. 2007, that’s how you do it, in earlier versions you Ctrl+C C, notice it’s there, Esc, and then I come right here and scoop this out, Ctrl+C, it’s loaded up, there’s the column and there’s the row, Esc. Come on over here, there is going to be a problem if we concatenate these two formulas, this is format right here, and formulas don’t see format. So we’re going to, right here, do =TEXT, and then we’ll just paste, this is our Row Header, our time, so inside of the TEXT, which will convert a number and format it and create text. So we’ll put comma, and we’ll put “hh:mm A/P”). Now we also are going to need to concatenate this, so I’m going to &, instead of just & I’m going to use CHAR(10) will also give me a hard return, so the date will jump down to the next line, and then another TEXT. We’ll paste this big huge formula right here, comma, and then we use the custom number format “m/d/y”), I got my fingers crossed, Ctrl+Shift+Enter!
Now we do need Word Wrap, right there, just because the hard return character will send it down to the next line, but we need word wrap to show it in the cells. Double-click and send it down, highlight all these rows and let’s see, do we get down here, for Truck9 we have 10AM on the 19th, 8AM on the 21st, alright. That’s the point for MrExcel, because that VBA, MUCH faster than that gigantic ridiculous formula, alright, see you next trick!
Bill: Hey Mike, that was really, really cool!
Now, I’m not sure I ever have to do a reverse lookup, but a couple of things I’ve learned in watching your tip. First of all, I never realized you can put a 0 as one of the arguments in INDEX, and have it return an entire array, either a column or a row, very, very cool.
Also, I loved your trick with the clipboard!
Showing that clipboard, so that way you can 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, the little dialog launcher in the bottom of the Clipboard group, love that one!
Hey, thanks Mike, and thanks to you for watching, we’ll see you next time for another netcast from MrExcel and ExcelIsFun!
