VLookup with range, range lookup

N

NewUser2

Board Regular
Joined
Jan 27, 2015
Messages
54
Good evening, I know there are previous posts about this, I have seen them and tried to use the info (and have got it to work before!), but it doesnt seem to way to cooperate with me now. Maybe one of you can see what's going wrong here and help me out.

Im trying to use Vlookup(98,A1:C12,3,TRUE) to return the result that corresponds between the numbers 100 and 95 (AB), as seen in the photo below. But for some reason its returning SM.

Thanks!

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]95[/TD]
[TD="width: 64"] AB[/TD]
[TD="width: 64"]SM[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]90[/TD]
[TD] BC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]85[/TD]
[TD] DE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]84[/TD]
[TD="align: right"]80[/TD]
[TD] FR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD="align: right"]75[/TD]
[TD] TY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD="align: right"]70[/TD]
[TD] JU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]69[/TD]
[TD="align: right"]65[/TD]
[TD] KI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]60[/TD]
[TD] LO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD="align: right"]55[/TD]
[TD] MN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]50[/TD]
[TD] WE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD="align: right"]1[/TD]
[TD] AS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] SM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
NewUser2 said:
Good evening, I know there are previous posts about this, I have seen them and tried to use the info (and have got it to work before!), but it doesnt seem to way to cooperate with me now. Maybe one of you can see what's going wrong here and help me out.

Im trying to use Vlookup(98,A1:C12,3,TRUE) to return the result that corresponds between the numbers 100 and 95 (AB), as seen in the photo below. But for some reason its returning SM.

Thanks!

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"]100
[/TD]
[TD="width: 64, align: right"]95
[/TD]
[TD="width: 64"] AB
[/TD]
[TD="width: 64"]SM
[/TD]
[/TR]
[TR]
[TD="align: right"]94
[/TD]
[TD="align: right"]90
[/TD]
[TD] BC
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89
[/TD]
[TD="align: right"]85
[/TD]
[TD] DE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]84
[/TD]
[TD="align: right"]80
[/TD]
[TD] FR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79
[/TD]
[TD="align: right"]75
[/TD]
[TD] TY
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]74
[/TD]
[TD="align: right"]70
[/TD]
[TD] JU
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]69
[/TD]
[TD="align: right"]65
[/TD]
[TD] KI
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]64
[/TD]
[TD="align: right"]60
[/TD]
[TD] LO
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]59
[/TD]
[TD="align: right"]55
[/TD]
[TD] MN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]54
[/TD]
[TD="align: right"]50
[/TD]
[TD] WE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49
[/TD]
[TD="align: right"]1
[/TD]
[TD] AS
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD] SM
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Try sorting the lookup column low to high
 
Upvote 0
Hey,

The following Index-Match equation will give you the value AB:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}</style>=INDEX(C:C,MATCH(98,A1:A12,-1))

In this equation match is finding the position of the smallest value that is greater than or equal to the look up value (98); in this case match finds the position of 100 which is 1. The index then pulls the 1st item in column C which is AB.

Hope that helps!
Andrew

NewUser2 said:
Good evening, I know there are previous posts about this, I have seen them and tried to use the info (and have got it to work before!), but it doesnt seem to way to cooperate with me now. Maybe one of you can see what's going wrong here and help me out.

Im trying to use Vlookup(98,A1:C12,3,TRUE) to return the result that corresponds between the numbers 100 and 95 (AB), as seen in the photo below. But for some reason its returning SM.

Thanks!

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]95[/TD]
[TD="width: 64"] AB[/TD]
[TD="width: 64"]SM[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]90[/TD]
[TD] BC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD="align: right"]85[/TD]
[TD] DE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]84[/TD]
[TD="align: right"]80[/TD]
[TD] FR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD="align: right"]75[/TD]
[TD] TY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD="align: right"]70[/TD]
[TD] JU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]69[/TD]
[TD="align: right"]65[/TD]
[TD] KI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]60[/TD]
[TD] LO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD="align: right"]55[/TD]
[TD] MN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]50[/TD]
[TD] WE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD="align: right"]1[/TD]
[TD] AS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] SM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
That works perfectly! Thanks a lot Andrew


AndrewHoffenberg said:
Hey,

The following Index-Match equation will give you the value AB:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}</style>=INDEX(C:C,MATCH(98,A1:A12,-1))

In this equation match is finding the position of the smallest value that is greater than or equal to the look up value (98); in this case match finds the position of 100 which is 1. The index then pulls the 1st item in column C which is AB.

Hope that helps!
Andrew
Click to expand...
 
Upvote 0
I found an error.... If I type in 105 as the lookup value it returns #N/A.
To combat this, I tried adding =IFERROR(INDEX(C:C,MATCH(98,A1:A12,-1)),INDEX(C:C,MATCH(98,A1:A12,1)))
Putting in the 1 for the error should switch to finding the largest value that is Less than the lookup value. So largest that is less than 105 is 100, so in theory that should work. However.... It returns SM....




AndrewHoffenberg said:
Hey,

The following Index-Match equation will give you the value AB:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}</style>=INDEX(C:C,MATCH(98,A1:A12,-1))

In this equation match is finding the position of the smallest value that is greater than or equal to the look up value (98); in this case match finds the position of 100 which is 1. The index then pulls the 1st item in column C which is AB.

Hope that helps!
Andrew
Click to expand...
 
Upvote 0
To those following this thread. I found a fix. invert the columns so they are ascending. Ignore the first column and bring the Match to the 2nd column. Then use 1 for the match type. I tried it out, and works for every number between the ranges.


NewUser2 said:
I found an error.... If I type in 105 as the lookup value it returns #N/A.
To combat this, I tried adding =IFERROR(INDEX(C:C,MATCH(98,A1:A12,-1)),INDEX(C:C,MATCH(98,A1:A12,1)))
Putting in the 1 for the error should switch to finding the largest value that is Less than the lookup value. So largest that is less than 105 is 100, so in theory that should work. However.... It returns SM....
Click to expand...
 
Upvote 0
Hey,

Right now the equation will give an error if the value is greater than 100. You can fix this with a simple iferror statement like:
=iferror(INDEX(C:C,MATCH(98,A1:A12,-1)),"AB")

I'm not sure the nature of you data and if 100 will always be the greatest value. You can also do something like this that checks if the value you are looking up is greater than the max value:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}</style>=IF(105>MAX(A1:A12),INDEX(C:C,MATCH(MAX(A1:A12),A1:A12,-1)),INDEX(C:C,MATCH(105,A1:A12,-1)))

Hope that helps,
Andrew
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K
J
  • Solved
Sorting occurrences of Postal Code in column / Sorting by occurrence high to low
Replies
7
Views
393
Fluff
Fluff
S
  • Solved
Calculate annual sales per country
Replies
5
Views
163
AhoyNC
A
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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