Absolute Reference not working

  • Thread starter Thread starter Iriemon
  • Start date Start date
I

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
 
Iriemon said:
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
Click to expand...


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.
 
Must have been the CSE when I changed the references, anyway it worked this
time.

Thanks

J
 
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?
 
Back
Top