Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Index Match Help Needed Badly

The index match that I was using I found did not work properly, much to
my dismay!
I was using this:
IF(ISNUMBER(MATCH($C60,s1.htm!$A:$A,0)),INDEX(s1.h tm!$E:$E,MATCH(F$2,s1.htm!$D:$D,0)),""),"Inactive" )

There is a "minor" if(index(match...preceding this, but that is not
where the problem is occuring....
What I found was that it was only giving me the 1st lookup it found and
kept returning that value down the range....

What I need this to do is; Find the store # in column A:A s1.htm that
corresponds to the store # in cell C60. Then find the answer I am
looking for in column E:E s1.htm and match that to that with the
question that is in F2 and s1.htm D:D

Obviously I did not do this correct. Can someone help me to figure this
one out?

Thanks a ALOT....got to get this working quickly


Hans J Hamm

  #2   Report Post  
Posted to microsoft.public.excel.misc
Hanr3
 
Posts: n/a
Default Index Match Help Needed Badly

Have you tried VLOOKUP?
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


" wrote:

The index match that I was using I found did not work properly, much to
my dismay!
I was using this:
IF(ISNUMBER(MATCH($C60,s1.htm!$A:$A,0)),INDEX(s1.h tm!$E:$E,MATCH(F$2,s1.htm!$D:$D,0)),""),"Inactive" )

There is a "minor" if(index(match...preceding this, but that is not
where the problem is occuring....
What I found was that it was only giving me the 1st lookup it found and
kept returning that value down the range....

What I need this to do is; Find the store # in column A:A s1.htm that
corresponds to the store # in cell C60. Then find the answer I am
looking for in column E:E s1.htm and match that to that with the
question that is in F2 and s1.htm D:D

Obviously I did not do this correct. Can someone help me to figure this
one out?

Thanks a ALOT....got to get this working quickly


Hans J Hamm


  #3   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Index Match Help Needed Badly

Hanr3....

I am not sure this will work....tried, but my experience with vlookup
IS limited!
the store # can and is repeated several times on the s1.htm sheet. So,
how would I go about doing it with this variable in the mix?

This is what I have
VLOOKUP(C81,s1.htm!$A:$F,5,FALSE)*VLOOKUP(E2,s1.ht m!$A:$F,5,FALSE)
C81(where the store is),lookup array,the answer what I am trying to
retrieve,False
E2(where the question is), lookup array, the answer I am trying to
retreive(this is the same answer as above), False

I must be missing something here 'cause what I get back is #Value


Thanks for the help

Hans

  #4   Report Post  
Posted to microsoft.public.excel.misc
Hanr3
 
Posts: n/a
Default Index Match Help Needed Badly

I don't think I understand what your looking for. Is each row a unique record?



Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


" wrote:

Hanr3....

I am not sure this will work....tried, but my experience with vlookup
IS limited!
the store # can and is repeated several times on the s1.htm sheet. So,
how would I go about doing it with this variable in the mix?

This is what I have
VLOOKUP(C81,s1.htm!$A:$F,5,FALSE)*VLOOKUP(E2,s1.ht m!$A:$F,5,FALSE)
C81(where the store is),lookup array,the answer what I am trying to
retrieve,False
E2(where the question is), lookup array, the answer I am trying to
retreive(this is the same answer as above), False

I must be missing something here 'cause what I get back is #Value


Thanks for the help

Hans


  #5   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Index Match Help Needed Badly

Hanr3...
Well I tried to be diligent in my work and it may have paid off! Taking
your advice and looking into any and all variations...I came up with
this.
Let me see if I can explain this...
I have 2 workbooks; 1st workbook contains sheets, "Summary and Market",
The 2nd has a single sheet, m1.htm.
Now in the 1.htm sheet the store # and question can be repeated
multiple times...That is where the problem was...

On the summary sheet I have one unique store number in column C; and
One unique Question in each column.
Now, for the workbook named m1.htm I have the store numbers in column
A. The store number can be repeated multiple times. Also, in m1.htm I
have in column E (notated by the 4) the answer to the question that I
need.

=if(INDEX('[Market]Market '!C:C,MATCH(Summary!$C37,'[Market]Market
'!$A:$A,0))="",IF(ISERROR(OFFSET(m1.htm!$A$1,MATCH ($C37,m1.htm!$A:$A,0)-1,4)),"",OFFSET(m1.htm!$A$1,MATCH($C37,m1.htm!$A:$ A,0)-1,4)),"Inactive")

I have gone through the numbers and this seems to work. BUT, and there
is always a but! The way I understand it is this; the first time the
match to the store is made it gives me the answer on the same row...but
should this not be 0 and not the -1? Then as it goes down the column it
goes from 0 to +1, +2 etc?? I have one summary that starts with a -1
then -0, +1, +2. The -1 is on the same row and then moves down the
column...

I thought I would post this back, just in case someone else runs across
this and to ask your help in understanding why this works, 'cause I am
not sure why?!

Thanks for your help and insight on leading me down the right path!


Hans

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
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
Index and Match Help Needed carl Excel Worksheet Functions 3 September 26th 05 09:42 PM
Index and match functions help needed. Zak Excel Worksheet Functions 5 September 1st 05 02:08 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 02:52 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"