VLookup with range, range lookup

[NewUser2]

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!

100	95	AB	SM
94	90	BC
89	85	DE
84	80	FR
79	75	TY
74	70	JU
69	65	KI
64	60	LO
59	55	MN
54	50	WE
49	1	AS
0	0	SM

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Brian from Maui]

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!

100	95	AB	SM
94	90	BC
89	85	DE
84	80	FR
79	75	TY
74	70	JU
69	65	KI
64	60	LO
59	55	MN
54	50	WE
49	1	AS
		SM

<tbody>
</tbody>

Try sorting the lookup column low to high

 

[AndrewHoffenberg]

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

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!

100	95	AB	SM
94	90	BC
89	85	DE
84	80	FR
79	75	TY
74	70	JU
69	65	KI
64	60	LO
59	55	MN
54	50	WE
49	1	AS
0	0	SM

<tbody>
</tbody>

 

[NewUser2]

That works perfectly! Thanks a lot Andrew

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

 

[AndrewHoffenberg]

Glad I could help!
-Andrew

 

[NewUser2]

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....

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]

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.

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]

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