Need help: To get the preferred data

[pranajit]

First of all I am very new and inexperienced, so kindly bear with me.

My problem:
I have the following table as source data

Term>>>>>>>>60 72 84 96 108 120 132 144 156 168 180 192 204 216 228 240
Factor>>>>>>> 5.4 6.25 7.1 7.95 8.8 9.65 10.5 11.35 12.2 13.05 13.95 14.8 15.7 16.6 17.5 18.45

Now need a formula so that if the term matches exactly then the corresponding factor is obtained

For e.g. Term> 60
Desired Factor > 5.4

But if the term is in between, factor returned must be the higher one

Say Term> 61
Desired factor> 6.25

I tried to use HLOOKUP but couldn't solve the program.

Please help

[FDibbins]

Hi, welcome to the forum

If you want an exact match, try this...

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Term	60	72	84	96	108	120	132	144	156	168	180	192	204	216	228	240
2	Factor	5.4	6.25	7.1	7.95	8.8	9.65	10.5	11.35	12.2	13.05	13.95	14.8	15.7	16.6	17.5	18.45
3
4
5	Term	60
6	Factor	5.4

B6=IFERROR(INDEX($B$2:$Q$2,MATCH(B5,$B$1:$Q$1,0)),"")

[amit.wilson]

See VLOOKUP higher value

Cheers

[FDibbins]

OK if you want it to jump to the next level if no exact, use this instead...
=IFERROR(INDEX($B$2:$Q$2,MATCH(B5,$B$1:$Q$1)+IF(HLOOKUP(B5,$B$1:$Q$1,1,1)<B5,1,0)),"")

[etaf]

Hlookup - if set to TRUE will use the largest value that is less than ‘lookup_value’.

so 61 will return 5.4

assuming
Term in A1:Q1
Factor in A2:Q2

the value to lookup is in A5

try using
=INDEX($B$2:$Q$2,MATCH(TRUE,INDEX($B1:$Q1>=$A$5,0),0))