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

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

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

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

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