|
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 |
Index and match
Hi Krish,
Could it be just $C$2 instead $C$4, at the very begining of your formula ... HTH Carim |
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 |
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 |
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 for another spreadsheet. The index data range is huge. Does these pose problems in my formula? Could any 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" <<A </A> 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 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 </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-- |
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 for another spreadsheet. The index data range is huge. Does these pose problems in my formula? Could any 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" <<A </A> 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 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 </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-- |
| All times are GMT +1. The time now is 02:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com