Look through the numbers in column B. When you find the first number greater than a hurdle value of 100, return the number from the corresponding value in column A. This Dueling Excel episodes offers tricks with INDEX, MIN, and MATCH.
Transcript of the video:
Hey, welcome back. It's time for another dueling excel podcast.
I'm Bill Jelen from Mr.Excel being joined by Mike Gervin from Excel Is fun.
This is our Episode # 150 - VLOOKUP >100 That's actually what I want to do, but it's not going to work.
You know Mike said hey, there are a lot of solutions to this and this is funny.
I come up with 39 solutions to it and the first 38 didn't work.
It's very very frustrating and from YouTube wants to look through column B. Find the first value that is greater than some arbitrary number.
Let's say a 100 and then return the corresponding cell from column A.
Wow, I'm not feeling very smart today.
So here's what I came up with. I'm going to say =IF look through all of these values in column B and if they're greater than a 100 then give me the row number of that cell.
Otherwise give me some large number. So I'm going to use 999 here because we only have 20 rows.
If you had 100,000 rows, you'd use 999999 and what that's going to do is that's going to return for us any time that the number is greater than 100 it's going to return the row number.
So row number 9 row number 13 row number 14 row number 15 and so on and then a whole bunch of 999s for all the small things.
So from there, I want to know the MIN.
Give me the MIN of all those numbers.
And we will press Enter.
Control + shift + Enter, and it says that we are in row 9.
So sure enough the first one that's greater than 100 is in row 9.
If we would put in something here that was greater than 100 and that automatically changes to show us which value, which row contains the first value greater than a 100.
From there then we will ask for the INDEX of column A.
Which I don't want. We want the answer from the MIN.
Ctrl + Shift + Enter the whole thing 165. We'll test here.We will make this B92.
So now we should be going down to the 189 and returning it to 117. Now as I said in the title. It wouldn't be nice if we could do a VLOOKUP and specify we're looking for the first thing > 100 or in this case would actually be a MATCH.
But that doesn't work and if you try and do the VLOOKUP.
The true version of VLOOKUP or the comma 1 Version of MATCH. It doesn't necessarily return the first one it returns one of them that is greater than 100 but not the first one.
So lots of failed attempts on my part.
Mike I'm to interested see what elegant beautiful formula you have to solve this.
Thanks, MrExcel.
Elegant beautiful, I love this.
That is beautiful and elegant.
Now I'm going to use a slightly different fromula but I'm still going to use the INDEX to lookup through that A column.
All right let's come over here. Alright. So the first thing if our hurdle is a hundred.
I'm going to go ahead and build a formula that asks the question.
Hey are any of you values here, and that's a bunch of values. Are you greater than our hurdle?
Now that's an operator acting on an array of items.
So when I highlight this and hit the F9 key it's an array operation which returns an array of values.
Notice TRUEs and FALSEs and what are we interested in.
We're interested in the first TRUE. Now notice there's lots of duplicates here. Ctrl+Z.
We want the first TRUE. No problem I can put that inside the MATCH function.
Now what am I looking up. The first TRUE. So the LOOKUP value will be TRUE comma.
That lookup array right there is an array operation that MATCH function cannot calculate this correctly to deliver the right answer to the cell unless, we enter it with Ctrl+ Shift+ Enter. That argument only will calculate correctly if we use Ctrl+Shift+Enter.
Now here's the trick for MATCH because we're interested in the first if we say Exact match that means it will if there are duplicates only get the first one which is the position 1 2 3.
The MATCH will deliver the relative position.
So if I hit Enter, not going to work, I have to use Ctrl+Shift+Enter There it is the third position.
Notice the curly brackets up here. We do Ctrl+Shift+ Enter to tell excel this is an array formula.
Those curly brackets or excel saying hey, I understood you wanted an array calculation.
Now I'm simply going to put that relative position into INDEX, the array, those are the values we want to lookup , row number we know that's going to be 3 that will be perfect.
Close parentheses.
Enter gives me an error because I have to use Ctrl+Shift and Enter.
Notice the curly brackets up there that means excel understood.
Now if you wanted to not have to remember to do Ctrl+ Shift+ Enter. We'll look at something silly here.
I'm going to copy this because that's a beautiful formula right there.
You don't use Ctrl+ Shift+ Enter, but watch this we can take this lookup array that is sitting in the lookup array argument inside of MATCH.
Now right now it's an array operation, so when MATCH gets it, it says I need Control+ Shift+ Enter But watch this we can convert it to, in a sense, a range by putting it inside of INDEX that argument can understand calculating array operations without Control+ Shift+ Enter But watch this what do we want, we want to deliver the whole column of values to MATCH. So I'm going to come to the end here and , for row number. If I leave it empty or put a 0 and I'm going to leave it empty that tells INDEX give me all the row numbers which will mean all the TRUEs and FALSEs.
So what we're doing here is we're tricking that argument can handle array operations without Control+ Shift+ Enter.
INDEX spits out a range of values that then Lookup array and MATCH can understand without Ctrl+ Shift+ Enter. So watch Enter.
All right throw it back to MrExcel.
Hey, All right Mike that is one cool formula that are using the INDEX with a blank row in order to avoid having to do Control + Shift + Enter right. Love it. All right well, I want to thank everyone for stopping by. Will see you next week for another dueling excel podcast.
MrExcel and Excel Is Fun
I'm Bill Jelen from Mr.Excel being joined by Mike Gervin from Excel Is fun.
This is our Episode # 150 - VLOOKUP >100 That's actually what I want to do, but it's not going to work.
You know Mike said hey, there are a lot of solutions to this and this is funny.
I come up with 39 solutions to it and the first 38 didn't work.
It's very very frustrating and from YouTube wants to look through column B. Find the first value that is greater than some arbitrary number.
Let's say a 100 and then return the corresponding cell from column A.
Wow, I'm not feeling very smart today.
So here's what I came up with. I'm going to say =IF look through all of these values in column B and if they're greater than a 100 then give me the row number of that cell.
Otherwise give me some large number. So I'm going to use 999 here because we only have 20 rows.
If you had 100,000 rows, you'd use 999999 and what that's going to do is that's going to return for us any time that the number is greater than 100 it's going to return the row number.
So row number 9 row number 13 row number 14 row number 15 and so on and then a whole bunch of 999s for all the small things.
So from there, I want to know the MIN.
Give me the MIN of all those numbers.
And we will press Enter.
Control + shift + Enter, and it says that we are in row 9.
So sure enough the first one that's greater than 100 is in row 9.
If we would put in something here that was greater than 100 and that automatically changes to show us which value, which row contains the first value greater than a 100.
From there then we will ask for the INDEX of column A.
Which I don't want. We want the answer from the MIN.
Ctrl + Shift + Enter the whole thing 165. We'll test here.We will make this B92.
So now we should be going down to the 189 and returning it to 117. Now as I said in the title. It wouldn't be nice if we could do a VLOOKUP and specify we're looking for the first thing > 100 or in this case would actually be a MATCH.
But that doesn't work and if you try and do the VLOOKUP.
The true version of VLOOKUP or the comma 1 Version of MATCH. It doesn't necessarily return the first one it returns one of them that is greater than 100 but not the first one.
So lots of failed attempts on my part.
Mike I'm to interested see what elegant beautiful formula you have to solve this.
Thanks, MrExcel.
Elegant beautiful, I love this.
That is beautiful and elegant.
Now I'm going to use a slightly different fromula but I'm still going to use the INDEX to lookup through that A column.
All right let's come over here. Alright. So the first thing if our hurdle is a hundred.
I'm going to go ahead and build a formula that asks the question.
Hey are any of you values here, and that's a bunch of values. Are you greater than our hurdle?
Now that's an operator acting on an array of items.
So when I highlight this and hit the F9 key it's an array operation which returns an array of values.
Notice TRUEs and FALSEs and what are we interested in.
We're interested in the first TRUE. Now notice there's lots of duplicates here. Ctrl+Z.
We want the first TRUE. No problem I can put that inside the MATCH function.
Now what am I looking up. The first TRUE. So the LOOKUP value will be TRUE comma.
That lookup array right there is an array operation that MATCH function cannot calculate this correctly to deliver the right answer to the cell unless, we enter it with Ctrl+ Shift+ Enter. That argument only will calculate correctly if we use Ctrl+Shift+Enter.
Now here's the trick for MATCH because we're interested in the first if we say Exact match that means it will if there are duplicates only get the first one which is the position 1 2 3.
The MATCH will deliver the relative position.
So if I hit Enter, not going to work, I have to use Ctrl+Shift+Enter There it is the third position.
Notice the curly brackets up here. We do Ctrl+Shift+ Enter to tell excel this is an array formula.
Those curly brackets or excel saying hey, I understood you wanted an array calculation.
Now I'm simply going to put that relative position into INDEX, the array, those are the values we want to lookup , row number we know that's going to be 3 that will be perfect.
Close parentheses.
Enter gives me an error because I have to use Ctrl+Shift and Enter.
Notice the curly brackets up there that means excel understood.
Now if you wanted to not have to remember to do Ctrl+ Shift+ Enter. We'll look at something silly here.
I'm going to copy this because that's a beautiful formula right there.
You don't use Ctrl+ Shift+ Enter, but watch this we can take this lookup array that is sitting in the lookup array argument inside of MATCH.
Now right now it's an array operation, so when MATCH gets it, it says I need Control+ Shift+ Enter But watch this we can convert it to, in a sense, a range by putting it inside of INDEX that argument can understand calculating array operations without Control+ Shift+ Enter But watch this what do we want, we want to deliver the whole column of values to MATCH. So I'm going to come to the end here and , for row number. If I leave it empty or put a 0 and I'm going to leave it empty that tells INDEX give me all the row numbers which will mean all the TRUEs and FALSEs.
So what we're doing here is we're tricking that argument can handle array operations without Control+ Shift+ Enter.
INDEX spits out a range of values that then Lookup array and MATCH can understand without Ctrl+ Shift+ Enter. So watch Enter.
All right throw it back to MrExcel.
Hey, All right Mike that is one cool formula that are using the INDEX with a blank row in order to avoid having to do Control + Shift + Enter right. Love it. All right well, I want to thank everyone for stopping by. Will see you next week for another dueling excel podcast.
MrExcel and Excel Is Fun
