How can I use LOOKUP, INDEX or MATCH (or any other lookup method) in this table when the values in Col C are for example "3900 kWh to 4,199 kWh"

[Canjosa]

The screen shot is one of a number of different tables that are nominated as "lookup tables". But column C is not a single value but a range.
I want to look up say 1550kWh (which will be just 1550) to find the corresponding values in the columns to the right. Is there a way to do this"

Thanks for your help!

 

[JoeMo]

Assuming col C is always in ascending order, you could add a helper column like col D in the example below and then use that column for lookup or match setting the match type to true (or 1). There may be better ways to do this depending on what version of Excel you are using.

Book1
	C	D
9		Energy Limit in kWh
10	0 kWh to 299 kWh	299
11	300 kWh to 599 kWh	599
12	600 kWh to 899 kWh	899
13	900 kWh to 1,199 kWh	1199
Sheet1

Cell Formulas
Range	Formula
D10:D13	D10	=MID(SUBSTITUTE(SUBSTITUTE(C10," kWh","")," to ",","),SEARCH(",",SUBSTITUTE(SUBSTITUTE(C10," kWh","")," to ",","))+1,LEN(C10))+0

 

[Bosquedeguate]

if you split the column with value ranges into two columns the VLOOKUP function would work well for your

Here is a short example. Your source table looks something like this ...

KWHSplit.xlsx
	A	B	C
1	kWh	Data	Data2
2	0 kWh to 299 kWh
3	300 kWh to 599 kWh
4	600 kWh to 899 kWh
Source Table

In another sheet or a different location on the same sheet split out the text ranges of values using a formula like the following

KWHSplit.xlsx
	A	B	C	D
1	KWH		Data	Data2
2	0	299
3	300	599
4	600	899
Split Table

Cell Formulas
Range	Formula
A2:A4	A2	=VALUE(LEFT('Source Table'!A2,FIND(" to ",SUBSTITUTE('Source Table'!A2, " kWh", ""))-1))
B2:B3	B2	=VALUE(SUBSTITUTE(MID('Source Table'!A2, FIND(" to ", 'Source Table'!A2)+4,10)," kWh",""))
B4	B4	=VALUE(SUBSTITUTE(MID('Source Table'!A4, FIND(" to ", 'Source Table'!A4)+4,11)," kWh",""))

then use
=VLOOKUP ( value, range of the table with split values, n, true)

this is a different version of using helper columns