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

R

Reboshua

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Eric - that worked perfectly. I didn't know you could do a nested match like that.

Much appreciated!

David
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
filter Top 5 by 2 criterions
2
Replies
10
Views
746
jasonb75
J
M
  • Question Question
I need piecewise regression
Replies
9
Views
11K
kalak
K
R
  • Question Question
lookup on calculated value not exact in list
Replies
5
Views
931
starkc69
S

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top