Look Up in Ranges

**jimd1768** · 11-19-2019, 05:49 AM

I have a table that I want to return a monetary value.

The result is a %. For example, if the % is 74% it falls within a range and should return a value of £1.50

I have attached the table to view.

I have tried an Hlookup but can't seem to return a value.

If someone could please cast an eye over and see if they could help me with this.

Thank you

**KOKOSEK** · 11-19-2019, 06:00 AM

You can't use HLOOKUP if you want to find NUMBER (0.68) in range of TEXT(ex. 0 - 0.49).
I suggest reorganized data like this:

	A	B	C	D	E	F	G
2			0.68	1.5
3
4	target range
5		0	0.49	0.59	0.79	0.99	1.24
6		£ -	£ 1.25	£ 1.50	£ 2.00	£ 2.50	£ 3.13

then:

	C	D
2	0.68	=HLOOKUP(C2,B5:G6,2,1)

**jimd1768** · 11-19-2019, 07:33 AM

Thank you for your help. Works perfectly.

J

**KOKOSEK** · 11-19-2019, 07:44 AM

Happy to help.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Thanks for rep.

Look Up in Ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Look Up in Ranges

Re: Look Up in Ranges

Re: Look Up in Ranges

Re: Look Up in Ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Use 2 Different Named Cell Ranges To Return Values From 1 Of the Ranges In Formula

Count rows with multiple criteria; comparing ranges and ranges to value in one statement

[SOLVED] VBA to accept pasting current named ranges into defined ranges

[SOLVED] I need to set ranges for Unique values, then from those ranges pick random cells...

Worksheet_Change procedure & multiple named ranges: specific cells/ranges fail to update

Copy data in named ranges to a newer version of the same template to identical ranges

[SOLVED] assigning ranks to number ranges:, what I am trying to do is turn ranges of scores

Bookmarks

Bookmarks

Posting Permissions