Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 16th 18, 10:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 2
Default 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?

  #2   Report Post  
Old July 16th 18, 10:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,624
Default 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
  #3   Report Post  
Old July 17th 18, 06:16 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 2
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Query using Match/Index Bara Reyna Excel Programming 2 January 15th 09 04:22 PM
Using INDEX and MATCH to find data in 2 different sheets RMF Excel Worksheet Functions 5 February 1st 06 01:02 PM
index,match,match on un-sorted data Brisbane Rob Excel Worksheet Functions 3 September 24th 05 10:04 PM
Using INDEX w/MATCH to get data from unsorted source Lynn Bales New Users to Excel 3 July 22nd 05 02:52 PM
Data Validation w/ If, Match & Index Statements Dominique Feteau Excel Worksheet Functions 2 December 18th 04 08:15 AM


All times are GMT +1. The time now is 12:34 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017