Robert asks how he can combine an HLOOKUP and a VLOOKUP together into a single formula. This is a common problem, but you don't use either VLOOKUP or HLOOKUP. Instead, Episode 884 will introduce a function called MATCH. This obscure but versatile function is similar to both VLOOKUP and HLOOKUP. Episode 884 shows you how to solve this problem.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well today's question comes from Robert.
Robert asked great question-- classic Excel question.
Robert says, "Hey, I need to do both a VLOOKUP and an HLOOKUP in the same formula." So he's trying to populate this table up here, and basically he needs to look over to Column A to figure out which Work Center, and then he needs to look up to Row 3 to figure out which particular date.
And his data area is down here-- he has a data area with a whole bunch of Work Centers along the left-hand side, not the same ones necessarily, or in the same order, as the report he's trying to fill out-- and then Dates going across the top-- and again, not necessarily the same order or anything like that.
So he has to do the VLOOKUP to figure out which row, and the HLOOKUP to figure out which column.
He says, "Okay, how are we going to do that?" Well, instead of using VLOOKUP or HLOOKUP, I want to introduce you to a function called MATCH, but don't freak out because MATCH is exactly like VLOOKUP or HLOOKUP; if you know how to do VLOOKUP or HLOOKUP, you're very comfortable with MATCH.
Here's a MATCH formula up here, and actually, let me edit this one.
It says, "Hey, look, we want to go find this value, A4, within this green range down here." And when you find it, we don't have to specify which column to return because it doesn't return it to a particular column like VLOOKUP would do.
You need the ,0 or ,FALSE at the end just like you would do in a VLOOKUP, to say we're looking for an exact match.
And what this is going to do is, it's going to look through a range and look for that A, and when it finds it, it's going to tell us that it's in the second position in that range.
Okay, now, hold on for a second, don't worry that-- who cares what position it's in?
It's all going to make sense in a second.
We're also, to simulate the HLOOKUP, going to use MATCH again.
This time, the MATCH says, "Hey, look, go look for this Date, 10/29, within this horizontal range." Put the ,0 at the end, which is like putting ,FALSE at the end of the HLOOKUP, and basically, what this is going to do, it's going to go through and say that 10/29 is the 9th item within the range.
Alright.
So, now, we have these two relatively useless bits of information-- which row is it on and which column is it on.
To put all that together, we're going to use a brand new function called INDEX.
Brand new-- well, it's been around forever, probably brand-new to most of us.
The INDEX function says, "Hey, we have this big range down here-- the blue range-- and I want a particular row number and a particular column number." So here I'm just, kind of, building a form that goes back and points to this row, and to this column, to return the 19-- which is, in fact, the value at the intersection of 10/29 and Work Center A. Simple enough.
Now, to put all this together-- and we're basically going to put an INDEX function together-- and where it asks for the row number, I'm going to put the MATCH; and were we're asked for the column number, I'm going to put the other MATCH; and I end up with one really big large formula down here that does the trick.
Now, once I've built that formula, kind of off to the side, and we move it up here and copy that formula throughout.
Now, Robert threw one more problem at me.
He said, "Sometimes the Work Center on the report is not in the data set, or sometimes the Date in the report is not in the data set." So I simulated that here.
There's a couple of rows where we're getting all N/A's, and so to solve that gets pretty ugly in the old Excel.
I'm going to choose the entire formula, press Ctrl+C, and then at the beginning I'm going to put =IF(ISNA( press Ctrl+V to paste the formula, ) to close the ISNA, and then ,"Missing Value",) and I have to put a closing parenthesis to close that initial IF statement that I added.
Copy that formula throughout the entire range and you see that, instead of N/A's, we get "Missing Values".
Alright, so it is possible to do both a VLOOKUP and an HLOOKUP together, but what you're going to do is, instead of the lookups, just use MATCH.
MATCH returns the position, and then finally put it all together with an INDEX.
And then, if you have the horrible problem where there's some values that are missing in the formula, that really kind of gets out of hand here-- you have to admit that anyone who comes along it just sees this formula, you know, they pick up the spreadsheet, their head will spin.
But, you know, basically build it here in three, four, five steps, and it all kind of makes sense when you see it put together bit, by bit, by bit.
At least, that's my hope.
Alright, well, hey, I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
Well today's question comes from Robert.
Robert asked great question-- classic Excel question.
Robert says, "Hey, I need to do both a VLOOKUP and an HLOOKUP in the same formula." So he's trying to populate this table up here, and basically he needs to look over to Column A to figure out which Work Center, and then he needs to look up to Row 3 to figure out which particular date.
And his data area is down here-- he has a data area with a whole bunch of Work Centers along the left-hand side, not the same ones necessarily, or in the same order, as the report he's trying to fill out-- and then Dates going across the top-- and again, not necessarily the same order or anything like that.
So he has to do the VLOOKUP to figure out which row, and the HLOOKUP to figure out which column.
He says, "Okay, how are we going to do that?" Well, instead of using VLOOKUP or HLOOKUP, I want to introduce you to a function called MATCH, but don't freak out because MATCH is exactly like VLOOKUP or HLOOKUP; if you know how to do VLOOKUP or HLOOKUP, you're very comfortable with MATCH.
Here's a MATCH formula up here, and actually, let me edit this one.
It says, "Hey, look, we want to go find this value, A4, within this green range down here." And when you find it, we don't have to specify which column to return because it doesn't return it to a particular column like VLOOKUP would do.
You need the ,0 or ,FALSE at the end just like you would do in a VLOOKUP, to say we're looking for an exact match.
And what this is going to do is, it's going to look through a range and look for that A, and when it finds it, it's going to tell us that it's in the second position in that range.
Okay, now, hold on for a second, don't worry that-- who cares what position it's in?
It's all going to make sense in a second.
We're also, to simulate the HLOOKUP, going to use MATCH again.
This time, the MATCH says, "Hey, look, go look for this Date, 10/29, within this horizontal range." Put the ,0 at the end, which is like putting ,FALSE at the end of the HLOOKUP, and basically, what this is going to do, it's going to go through and say that 10/29 is the 9th item within the range.
Alright.
So, now, we have these two relatively useless bits of information-- which row is it on and which column is it on.
To put all that together, we're going to use a brand new function called INDEX.
Brand new-- well, it's been around forever, probably brand-new to most of us.
The INDEX function says, "Hey, we have this big range down here-- the blue range-- and I want a particular row number and a particular column number." So here I'm just, kind of, building a form that goes back and points to this row, and to this column, to return the 19-- which is, in fact, the value at the intersection of 10/29 and Work Center A. Simple enough.
Now, to put all this together-- and we're basically going to put an INDEX function together-- and where it asks for the row number, I'm going to put the MATCH; and were we're asked for the column number, I'm going to put the other MATCH; and I end up with one really big large formula down here that does the trick.
Now, once I've built that formula, kind of off to the side, and we move it up here and copy that formula throughout.
Now, Robert threw one more problem at me.
He said, "Sometimes the Work Center on the report is not in the data set, or sometimes the Date in the report is not in the data set." So I simulated that here.
There's a couple of rows where we're getting all N/A's, and so to solve that gets pretty ugly in the old Excel.
I'm going to choose the entire formula, press Ctrl+C, and then at the beginning I'm going to put =IF(ISNA( press Ctrl+V to paste the formula, ) to close the ISNA, and then ,"Missing Value",) and I have to put a closing parenthesis to close that initial IF statement that I added.
Copy that formula throughout the entire range and you see that, instead of N/A's, we get "Missing Values".
Alright, so it is possible to do both a VLOOKUP and an HLOOKUP together, but what you're going to do is, instead of the lookups, just use MATCH.
MATCH returns the position, and then finally put it all together with an INDEX.
And then, if you have the horrible problem where there's some values that are missing in the formula, that really kind of gets out of hand here-- you have to admit that anyone who comes along it just sees this formula, you know, they pick up the spreadsheet, their head will spin.
But, you know, basically build it here in three, four, five steps, and it all kind of makes sense when you see it put together bit, by bit, by bit.
At least, that's my hope.
Alright, well, hey, I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
