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--
|