INDEX, MATCH Formula for Latest Value Problem

[applefritter]

Hi,
I have a formula I've used before in another workbook thats not working now in a new workbook I've created.

I need to populate a cell (B4) on sheet (Cleanings) based on lastest value in column Y from sheet (RawData):
=INDEX(RawData!$Y:$Y,MATCH(9.99999999999999+307,RawData!$Y:$Y))

Column Y cells are calculated cells based on data from cells in columns B, T and X on the (RawData) sheet. These calculations run all the way down to row 500. I also have conditional formatting that will grey out the cell in column Y if cells in columns B, T or X are empty. Currently I have data down thru row 66 so rows 67-500 are greyed out. My issue is the following:

Cell B4 (Cleanings) is populated with data from cell Y48 instead of Y66. If I delete row 48 (RawData), then B4 displays data from Y37 (RawData). If I delete row 37 then it displays Y36 (RawData). I can't make any sense of what it is doing since all cells are popululated with data thru row 66. Any idea?

 

[pgc01]

Hi
Welcome to the board

Did you check that all the data are number values?

For ex., for Y49, write in another cell

=ISNUMBER(Y49)

Does it result in TRUE?

 

[applefritter]

Thank you for your response. Yes, I did. They're all TRUE.

 

[AhoyNC]

What happens if you change your formula to:

=INDEX(RawData!$Y:$Y,MATCH([COLOR=#FF0000]9.99999999999999E[/COLOR]+307,RawData!$Y:$Y))

 

[pgc01]

Good catch, AhoyNC.

 

[applefritter]

That seems to have done the trick. Thanks!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.