Absolute Reference not working

[Iriemon]

I have this formula:

=INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2:BF94&'Sheet2'!BG2:BG94,0))
(committed w/ Control Shift Enter)

and it works great.

However, when I try to put in absolute referencing I get a #N/A error

=INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'!$BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0))

Control Shift Enter will not even commit this formula to the cell.

What am I doing wrong .

Thanks

J

 

[Glenn]

I have this formula:

=INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2:BF94&'Sheet2'!BG2:BG94,0))
(committed w/ Control Shift Enter)

and it works great.

However, when I try to put in absolute referencing I get a #N/A error

=INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'!$BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0))

Control Shift Enter will not even commit this formula to the cell.

What am I doing wrong .

Thanks

J

Works for me.

To eliminate possible typos, when adding the absolute referencing, just click on
the cell reference within the formula and hit F4 to cycle through the options.

When finished, commit with CTRL+SHIFT+ENTER.

 

[Iriemon]

Must have been the CSE when I changed the references, anyway it worked this
time.

Thanks

J

 

[Iriemon]

One last question:

How would this be modified to find the closest or next smaller value?

For example, if I am trying to match .625 and 5000 and the table has:


..625 4000 .55
..625 4750 .65
..625 5500 .25
..625 6000 .35

How do I get it to return the .65?