theoretical size question

**excellentexcel** · 01-16-2009, 04:25 PM

hi all
just a theoretical question really
would excel be able to work this out?

in cell a1 i have 1-5
b1 i have 6-9

in a2 i have £4.99
in b2 i have £9.99

if in cell d1 i input the number 4 would it be able to understand that 4 is in the group 1-5? and should produce £4.99 in cell d2?

thanks
if it is possible and you could point me the right way ti would be appreciated

**jasoncw** · 01-16-2009, 04:35 PM

Sure, many things are possible in Excel.

Here is one solution, although there may be a more simple method:

=SUMPRODUCT((D1>=VALUE(LEFT(A1:B1,SEARCH("-",A1:B1)-1)))*(D1<=VALUE(MID(A1:B1,SEARCH("-",A1:B1)+1,1^99)))*(A2:B2))

HTH

Jason

**excellentexcel** · 01-16-2009, 04:39 PM

wow thanks jason
i really didnt think it tould understand that
thanks for your help

**jasoncw** · 01-16-2009, 04:52 PM

Glad it helped.

**broro183** · 01-16-2009, 05:47 PM

An alternative approach can use vlookup's "closest match" (the "true" in the vlookup - check Help for details). Type 1 into cell A1 & 6 into cell A2 and put this formula into D2

Please Login or Register  to view this content.

At the moment, anything greater than 6 will return 9.99, but the result could be changed by typing 10 into cell A3 & "please enter a number between 1 & 9" into cell B3, then changing the D2 formula to

Please Login or Register  to view this content.

. This effectively"caps" the input range.

hth
Rob

**jasoncw** · 01-16-2009, 06:01 PM

You meant HLOOKUP, right, Rob?

**excellentexcel** · 01-16-2009, 07:08 PM

jason just as an update
i need some help along the same lines if you would be so kind

if i needed to enter a number in cell e1 and there is a y in cell h1 it would need to return the red section in this case £9.99

but if there was an n in cell h1 it would need to return the yellow section which in this case would be £1.99

any ideas please

please find attached

**deadlyduck** · 01-16-2009, 07:40 PM

You can achieve this by modifying the existing formula to the following:

Please Login or Register  to view this content.

Hope this helps.

**excellentexcel** · 01-16-2009, 07:53 PM

quality

thanks thats perfect

**deadlyduck** · 01-16-2009, 08:01 PM

You're welcome and thanks for the feedback!

**broro183** · 01-18-2009, 01:22 AM

This thread is tagged, bagged & solved but just to clarify for Jason...

Originally Posted by jasoncw

You meant HLOOKUP, right, Rob?

Nope, I do mean VLookup - try out my suggestion (or view attachment) & you'll see that it works (& will work for any sized list of numbers as long as they are in ascending order)...

As the Excel 2003 Help Files state for Vlookup:

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The V in VLOOKUP stands for "Vertical."

Rob

**jasoncw** · 01-18-2009, 05:33 PM

Sorry for the confusion, Rob. I was assuming you were leaving the data as arranged by the OP (lookup values in row 1, results in row 2), which would require HLookup instead of VLookup.