Lookup Table To Return Desired Result

[beadedbytes]

Is there a way in Excel to create a lookup table that would evaluate criteria and return the desired result? Please see below for lookup table and desired result examples. Or, let me know if there is a different solution. Thank you in advance.

An example of the lookup table:
Col A Col B
$10 $10-$14
$15 $15-$24.99
$25 $25-$49.99
$50 $50-$99.99



Criteria To Result
Evaluate
$7 No result
$20 $15-$24.99
$50 $50-$99.99
$120 No result

 

[GlennUK]

Col A Col B
$0 No Result
$10 $10-$14
$15 $15-$24.99
$25 $25-$49.99
$50 $50-$99.99
$9999999 No Result

Look at the VLOOKUP function. It will be a formula similar to this:

=VLOOKUP(CriteriaCell,A1:B7,2,TRUE)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Sorry, went berserk with the 9's there ... last entry should read 99.999

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[beadedbytes]

I should have mentioned that I tried VLOOKUP and it returns an #NA error message.

The table mentioned in my thread is in cells A1:B4 on my worksheet. Criteria cell containing a value of 20 is in cell D1. In cell E1, I wrote the following VLOOKUP formula: VLOOKUP(D1,$A$1:$A$4,2,TRUE). The formula returned #NA as the result instead of $15-$24.99.

What am I missing?

On a different note, Glenn, I have a 'mechanical' question. How were you able to paste the 'CODE' info in your response into the Tek-Tip message block? I've tried a couple of different ways and have had no success. Feedback would be much appreciated.

 

[GlennUK]

Your table is $A$1:$B$4 not $A$1:$A$4. That might fix it.

To put code inside a proper code posting area use tags like

 before your code and the same afterwards except with a / before the word code.

Cheers, Glenn.

Did you hear about the literalist show-jumper?  He broke his nose jumping against the clock.

 

[beadedbytes]

My table range in the VLOOKUP formula is $A$1:$B$4 (typo in my last posting). Still does not work for some reason.

Thanks for the quick response!

Also, thanks for the solution regarding 'code'. Will try it out.

 

[Gavona]

20 is a number.
$10 etc in your lookup table is text rather than a number formatted to display with a leading $ sign. (Probably you can tell because text is left aligned).

You need to be consistent.


Gavin

 

[beadedbytes]

Thank you both!

Gavona --
You were absolutely right! The formats were different.

Glenn --
Thanks for the formula solution.


The only issue occurring now is that, based on my initial post, the result for $120 is not correct. It's returning $50-$99.99