Index and match

  • Thread starter Thread starter Krish
  • Start date Start date
K

Krish

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
 
Hi Krish,

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

HTH
Carim
 
$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.
Hi Krish,

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

HTH
Carim
 
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?
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
 
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
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?
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=2>The 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 href="mailto:[email protected]">[email protected]</A>&gt;
wrote in message <A
href="<DIV><FONT face=Arial size=2>I 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=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2>The 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=2>Please help me.</FONT></DIV>
<DIV><FONT face=Arial size=2>Thanks.</FONT></DIV>
<DIV><FONT face=Arial size=2>Krish</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0141_01C6E963.90CD0FF0--
Click to expand...
 
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.
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
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?
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=2>The 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 href="mailto:[email protected]">[email protected]</A>&gt;
wrote in message <A
href="<DIV><FONT face=Arial size=2>I 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=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2>The 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=2>Please help me.</FONT></DIV>
<DIV><FONT face=Arial size=2>Thanks.</FONT></DIV>
<DIV><FONT face=Arial size=2>Krish</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0141_01C6E963.90CD0FF0--
Click to expand...
 
Back
Top