Vlookup or something else?

[LydiaF]

Hello all,

I have a spread sheet with three columns. If I have the value "29378" in a cell, I want to be able to return "301". In other words,

What is the value for Pen If LookUp Variable (29378 for example) >= Start Band AND <= End Band

In Access I would use DLOOKUP and be done with it. It would save some effort if we could do it in Excel, however.


<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>PEN</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Start Band</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>End Band</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>301</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29377</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29408</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>302</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29409</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29440</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>303</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29441</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>29472</TD></TR></TBODY></TABLE>

Thanks for your help!

Lydia

 

[Aladin Akyurek]

=LOOKUP(LookupValue,StartRange,PENrange)

might suffice...

 

[LydiaF]

Thank You! Suffice it did!

 

[LydiaF]

Ooof! I spoke too soon, I'm afraid...

It's important that the formula check for the range as they are not always in sequential order.

For example, using the small dataset I posted in the first post, suppose the endband for 301 was 29405 instead. If my variable is equal to 29406, I would want to return "N/A" or "Not Found" or somesuch. Using Lookup() will return 301, which would be incorrect.

Any help is appreciated. In the meantime, I'll keep plugging away.

LydiaF

 

[ZVI]

Hi Lydia,
Here are two variants for you:

Excel Workbook
	A	B	C
1	PEN	Start Band	End Band
2	301	29377	29405
3	302	29409	29440
4	303	29441	29472
5
6	29403		301
7			301
Sheet1

Vladimir

 

[LydiaF]

Thank you!

What a great resource this forum has turned out to be...

 

[LydiaF]

Interesting...

In case anyone else needs the same kind of formula, the Lookup formula provided by ZVI worked in Excel 2000 (my work computer). The Sumproduct formula worked in Excel 2003 (home). I couldn't get Sumproduct to work in Excel 2000, to my chagrin.

 

[LydiaF]

Disregard the above post. Both formulas worked in Excel 2000. I need to pay attention to my signage.

 

[ZVI]

Yes, it works in all version of MS Excel, even in Excel 97.
But please take into account difference in returned value if not found:
LOOKUP returns #N/A
SUMPRODUCT returns 0 (zero)