INDEX MATCH formulas on using more than 1 sheet

Hi-

I've been struggling for days to try and find a formula that will track the data on my dashboard sheet that comes from other sheets. What I am looking for is a formula that will show me on sheet 1 the last numeric value of a cell in a specific row of sheet 2. I found a formula that works when you I put it in a cell in the same sheet it is referencing but not when I put it on the dashboard sheet. On the dashboard sheet it keeps giving me a 0.

the formula is =INDEX(B2:Z2,MATCH(9.99999999999999E+307,B2:Z2))

I've attached a sample spreadsheet. I want the B column of the sheet titled Dashboard to reference the most current value on the city 1 tab.

Can you help me please?

Try this in B2 of the Dashboard sheet.

=LOOKUP(9.99999999999999E+307,INDEX(city1!$B$2:$D$4,MATCH(A2,city1!$A$2:$A$4,0),0))

For example: on the Dashboard sheet under Kristine it should read 25, since 25 is the last non-blank cell in Row 2 (Kristine) on the City 1 sheet.

awesome!! thanks so much!

You're welcome.

ok, so the sample worked perfectly. But when I tried to use the formula for my actual spreadsheet it didn't work. I updated my sample sheet to have the same spacing and text as my actual document (which is actually a google doc). Can you help me figure out how to make it work again?sample2.xlsx

C8 on the Dashboard sheet should be

=LOOKUP(9.99999999999999E+307,INDEX('HN15'!$B$8:$D$10,MATCH(A8,'HN15'!$A$8:$A$10,0),0))

for some reason it still comes up as N/A with this message Error: Did not find value '# of Alumni ' in MATCH evaluation.

Works for me

EFkmovalli.xlsx

i wonder if it has something to do with the fact that its a google doc?