Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Index and match
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default Index and match
I am having trouble getting the desired results using Index Match
The formula is as follows.
=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)

If I select the "Match" and test the function I am getting the row number and coulmn number. However if I select with Index for the Data range, I am getting "#Ref" as the result.
The data is a Pivot Table and it has 217columns and 88 rows.Will that pose problem using Index and Match?
Please help me.
Thanks.
Krish
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 510
Default Index and match
Hi Krish,

Could it be just $C$2 instead $C$4, at the very begining of your
formula ...

HTH
Carim

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default Index and match
$C$2 is the Labels for the Columns. My data starts from $C$4. As suggested, I tried using $C$2, but did not help.
"Carim" wrote in message oups.com...
Hi Krish,

Could it be just $C$2 instead $C$4, at the very begining of your
formula ...

HTH
Carim

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default Index and match
The exact match lookup for both rows and columns is looking for another spreadsheet. The index data range is huge. Does these pose problems in my formula? Could any MVPs help me please?
"Krish" wrote in message ...
I am having trouble getting the desired results using Index Match
The formula is as follows.
=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)

If I select the "Match" and test the function I am getting the row number and coulmn number. However if I select with Index for the Data range, I am getting "#Ref" as the result.
The data is a Pivot Table and it has 217columns and 88 rows.Will that pose problem using Index and Match?
Please help me.
Thanks.
Krish
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,856
Default Index and match
What is your first MATCH trying to do? Essentially, stripping away the
file and sheet names, you have:

=MATCH(C2,C2:D2,0)

Won't this always return a 1, and is that what you want?

Hope this helps.

Pete

Krish wrote:
The exact match lookup for both rows and columns is looking for another spreadsheet. The index data range is huge. Does these pose problems in my formula? Could any MVPs help me please?
"Krish" wrote in message ...
I am having trouble getting the desired results using Index Match
The formula is as follows.
=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)

If I select the "Match" and test the function I am getting the row number and coulmn number. However if I select with Index for the Data range, I am getting "#Ref" as the result.
The data is a Pivot Table and it has 217columns and 88 rows.Will that pose problem using Index and Match?
Please help me.
Thanks.
Krish
------=_NextPart_000_0141_01C6E963.90CD0FF0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2011

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1"
<META content="MSHTML 6.00.3790.2759" name=GENERATOR
<STYLE</STYLE
</HEAD
<BODY bgColor=#ffffff
<DIV<FONT face=Arial size=2The exact match lookup for both rows and columns is
looking&nbsp;for another spreadsheet. The index data range is huge. Does these
pose problems in my formula? Could any&nbsp;MVPs help me please?</FONT</DIV
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"
<DIV"Krish" &lt;<A </A&gt;
wrote in message <A
href="news:66Sdnel5Vf_8DLvYnZ2dnUVZ_s6dnZ2d@megapa th.net"news:66Sdnel5Vf_8DLvYnZ2dnUVZ_s6dnZ2d@mega path.net</A...</DIV
<DIV<FONT face=Arial size=2I am having trouble getting the desired results
using Index Match</FONT</DIV
<DIV<FONT face=Arial size=2&nbsp;The formula is as follows.</FONT</DIV
<DIV<FONT face=Arial
size=2=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand
Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)</FONT</DIV
<DIV<FONT face=Arial size=2</FONT&nbsp;</DIV
<DIV<FONT face=Arial size=2If I select the "Match" and test the function I
am getting the row number and coulmn number. However if I select with Index
for the Data range, I am getting "#Ref" as the result.</FONT</DIV
<DIV<FONT face=Arial size=2The data is a Pivot Table and it has 217columns
and 88 rows.Will that pose problem using Index and Match?</FONT</DIV
<DIV<FONT face=Arial size=2Please help me.</FONT</DIV
<DIV<FONT face=Arial size=2Thanks.</FONT</DIV
<DIV<FONT face=Arial size=2Krish</FONT</DIV</BLOCKQUOTE</BODY</HTML

------=_NextPart_000_0141_01C6E963.90CD0FF0--

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default Index and match
Row 2 contain the date in the format 10/04/2006. Instead of writing '10/04/2006', I used the specific reference and I got the results for that Match as 217, which is the correct column. I did not get '1' as you indicate.
"Pete_UK" wrote in message oups.com...
What is your first MATCH trying to do? Essentially, stripping away the
file and sheet names, you have:

=MATCH(C2,C2:D2,0)

Won't this always return a 1, and is that what you want?

Hope this helps.

Pete

Krish wrote:
The exact match lookup for both rows and columns is looking for another spreadsheet. The index data range is huge. Does these pose problems in my formula? Could any MVPs help me please?
"Krish" wrote in message ...
I am having trouble getting the desired results using Index Match
The formula is as follows.
=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)

If I select the "Match" and test the function I am getting the row number and coulmn number. However if I select with Index for the Data range, I am getting "#Ref" as the result.
The data is a Pivot Table and it has 217columns and 88 rows.Will that pose problem using Index and Match?
Please help me.
Thanks.
Krish
------=_NextPart_000_0141_01C6E963.90CD0FF0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2011

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1"
<META content="MSHTML 6.00.3790.2759" name=GENERATOR
<STYLE</STYLE
</HEAD
<BODY bgColor=#ffffff
<DIV<FONT face=Arial size=2The exact match lookup for both rows and columns is
looking&nbsp;for another spreadsheet. The index data range is huge. Does these
pose problems in my formula? Could any&nbsp;MVPs help me please?</FONT</DIV
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"
<DIV"Krish" &lt;<A </A&gt;
wrote in message <A
href="news:66Sdnel5Vf_8DLvYnZ2dnUVZ_s6dnZ2d@megapa th.net"news:66Sdnel5Vf_8DLvYnZ2dnUVZ_s6dnZ2d@mega path.net</A...</DIV
<DIV<FONT face=Arial size=2I am having trouble getting the desired results
using Index Match</FONT</DIV
<DIV<FONT face=Arial size=2&nbsp;The formula is as follows.</FONT</DIV
<DIV<FONT face=Arial
size=2=INDEX([RMSDaily.xls]Department!$C$4:$D$87,MATCH([RMSDaily.xls]Department!$C$2,[RMSDaily.xls]Department!$C$2:$D$2,0),MATCH("Grand
Total",[RMSDaily.xls]Department!$A$2:$A$87,0),1)</FONT</DIV
<DIV<FONT face=Arial size=2</FONT&nbsp;</DIV
<DIV<FONT face=Arial size=2If I select the "Match" and test the function I
am getting the row number and coulmn number. However if I select with Index
for the Data range, I am getting "#Ref" as the result.</FONT</DIV
<DIV<FONT face=Arial size=2The data is a Pivot Table and it has 217columns
and 88 rows.Will that pose problem using Index and Match?</FONT</DIV
<DIV<FONT face=Arial size=2Please help me.</FONT</DIV
<DIV<FONT face=Arial size=2Thanks.</FONT</DIV
<DIV<FONT face=Arial size=2Krish</FONT</DIV</BLOCKQUOTE</BODY</HTML

------=_NextPart_000_0141_01C6E963.90CD0FF0--

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Index, Match and filters Steve M Excel Discussion (Misc queries) 4 August 22nd 06 09:12 PM
Match Index cjjoo Excel Worksheet Functions 3 October 25th 05 09:33 AM
Match or Index Question carl Excel Worksheet Functions 2 October 4th 05 09:11 PM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 03:26 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 06:11 PM


All times are GMT +1. The time now is 08:19 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement