Hi

I am trying to match a value within another sheet i.e. a position in another sheet based basically on column and row, but this will vary depending on what is selected.

https://docs.google.com/spreadsheets...#gid=462775805

This is the sheet. You can see in the total column, i have used index and match and used match to return the row and column, depending on what is selected.

What i am confused about is why the first one returns correct but the other values are just blank?

Hi Jon,

Am Mon, 16 Jul 2018 14:03:38 -0700 (PDT) schrieb :


I am trying to match a value within another sheet i.e. a position in another sheet based basically on column and row, but this will vary depending on what is selected.

https://docs.google.com/spreadsheets...#gid=462775805

This is the sheet. You can see in the total column, i have used index and match and used match to return the row and column, depending on what is selected.

What i am confused about is why the first one returns correct but the other values are just blank?

you have to set the INDEX range absolut. And you must /not/ enter the
formula with CTRL+Shift+Enter.
Try:
=INDEX(Sheet1!$A$17:$F$19,MATCH(C2,Sheet1!$A$17:$A $19,0),MATCH(B2,Sheet1!$A$1:$F$1,0))


Regards
Claus B.
--
Windows10
Office 2016

On Monday, 16 July 2018 22:17:02 UTC+1, Claus Busch wrote:
Hi Jon,

Am Mon, 16 Jul 2018 14:03:38 -0700 (PDT) schrieb

I am trying to match a value within another sheet i.e. a position in another sheet based basically on column and row, but this will vary depending on what is selected.

https://docs.google.com/spreadsheets...#gid=462775805

This is the sheet. You can see in the total column, i have used index and match and used match to return the row and column, depending on what is selected.

What i am confused about is why the first one returns correct but the other values are just blank?

you have to set the INDEX range absolut. And you must /not/ enter the
formula with CTRL+Shift+Enter.
Try:
=INDEX(Sheet1!$A$17:$F$19,MATCH(C2,Sheet1!$A$17:$A $19,0),MATCH(B2,Sheet1!$A$1:$F$1,0))


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

Thanks for that, that did the trick.