How to search in a grid, when you know 2 variables, kind of...

[Reboshua]

Hi - I am trying to come up with a formula for R15.

The variables I know are P15 and Q15.

P15 will refer to the values in column D and I need to find values in the columns where Q15 falls between, so, in this case, we'd be looking at column H. 621 is less than 650, but greater than 600.

I've never tried to do vlookups in a grid with multiple variables.

Thanks!



​Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​		500​	550​	600​	650​	700​	750​	800​	850​	900​	950​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​	1​	4.51​	4.55​	4.62​	4.63​	4.68​	4.72​	4.75​	4.8​	4.86​	4.9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​	2​	4.42​	4.45​	4.51​	4.52​	4.57​	4.61​	4.64​	4.69​	4.77​	4.8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​	3​	4.34​	4.36​	4.41​	4.42​	4.46​	4.51​	4.53​	4.58​	4.66​	4.7​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​	4​	4.23​	4.26​	4.31​	4.32​	4.36​	4.4​	4.42​	4.49​	4.54​	4.56​		2​	621​	4.52​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	5​	4.12​	4.16​	4.21​	4.22​	4.26​	4.3​	4.31​	4.38​	4.44​	4.52​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​	6​	4​	4.04​	4.09​	4.11​	4.16​	4.2​	4.21​	4.27​	4.33​	4.43​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​	7​	3.86​	3.92​	3.99​	4​	4.04​	4.08​	4.1​	4.16​	4.21​	4.26​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​	8​	3.76​	3.81​	3.86​	3.87​	3.92​	3.96​	3.99​	4.04​	4.1​	4.12​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​	9​	3.62​	3.68​	3.74​	3.76​	3.81​	3.84​	3.87​	3.92​	3.98​	3.99​

<tbody>
</tbody>

Sheet: PAperBO

<tbody>
</tbody>

 

[Eric W]

I think I have the ranges right:

=INDEX(E12:N20,MATCH(P15,D12:D20),MATCH(Q15,E11:N11))

 

[Reboshua]

Eric - that worked perfectly. I didn't know you could do a nested match like that.

Much appreciated!

David

 

[Eric W]

Happy to help!