Help with Index and Match with data validation
 

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?

Help with Index and Match with data validation
 

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

Help with Index and Match with data validation
 

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.