Excel 2002 SP3
Win XP HE SP1

*Follow-up group is: microsoft.public.excel* *only reply to:
microsoft.public.excel*

hi,
i am having a problem with vlookup wherein if i use formula A:
=VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
=VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
the only difference is the table array range which in the formula B
looks into the same sheet etc., but at more columns.
i can't understand what is causing the problem.
i believe that the columns do not need to be sorted (help file); the
sheets are formatted properly, etc.
i've done LOTS of vlookups in really complex spreadsheets in the past
but am rusty right now and can't put my finger on what i am doing wrong.
anyone can help?

You sure
=VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
shouldn't be:
=VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)

(so the key looks to match column C).



stef wrote:
>
> Excel 2002 SP3
> Win XP HE SP1
>
> *Follow-up group is: microsoft.public.excel* *only reply to:
> microsoft.public.excel*
>
> hi,
> i am having a problem with vlookup wherein if i use formula A:
> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> the only difference is the table array range which in the formula B
> looks into the same sheet etc., but at more columns.
> i can't understand what is causing the problem.
> i believe that the columns do not need to be sorted (help file); the
> sheets are formatted properly, etc.
> i've done LOTS of vlookups in really complex spreadsheets in the past
> but am rusty right now and can't put my finger on what i am doing wrong.
> anyone can help?

--

Dave Peterson

hi dave,
tx 4 picking this up. i'm desperate.... it must be making me stupid
and ignorant as well.......
yes, i'm sure.
i am looking for a match of a cell in column a (or b or c--so A2 or B2
or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
i want the value in column e of lookup range/table array to be returned
in cell where i put vlookup formula.........

similar to =VLOOKUP($B3,table_array,3,FALSE)

what am i doing wrong?

PS: incidentally, i do not understand why ur reply went to both
newsgroups microsoft.public.excel and microsoft.public.excel.functions
as i had set up the post to only reply to or follow-up to
microsoft.public.excel. is it me or did u add urself the
microsoft.public.excel.functions to the reply? (i'm just trying to find
out as i do not want to multi post, etc....)

Dave Peterson wrote:
> You sure
> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> shouldn't be:
> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
>
> (so the key looks to match column C).
>
>
>
> stef wrote:
>> Excel 2002 SP3
>> Win XP HE SP1
>>
>> *Follow-up group is: microsoft.public.excel* *only reply to:
>> microsoft.public.excel*
>>
>> hi,
>> i am having a problem with vlookup wherein if i use formula A:
>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>> the only difference is the table array range which in the formula B
>> looks into the same sheet etc., but at more columns.
>> i can't understand what is causing the problem.
>> i believe that the columns do not need to be sorted (help file); the
>> sheets are formatted properly, etc.
>> i've done LOTS of vlookups in really complex spreadsheets in the past
>> but am rusty right now and can't put my finger on what i am doing wrong.
>> anyone can help?
>

I didn't read the original message--just the 2nd followup.

It kind of looks like you want formulas like:

=index(sheet2!a:a,match(c1,sheet2!c:c,0))
=index(sheet2!b:b,match(c1,sheet2!c:c,0))
=index(sheet2!d:d,match(c1,sheet2!c:c,0))
=index(sheet2!e:e,match(c1,sheet2!c:c,0))

The key is in column C of both sheets and you want to return the value in
columns a, b, d, e when there's a match.

Debra also has notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

I didn't see any followup settings when I looked at the headers for the message
I replied to. But that seems ok to me. If you cross post to a couple of
newsgroups, the newsgroup that doesn't get the responses will look like the
question still needs to be answered.

In my opinion, you don't even need to cross post. Just pick the newsgroup that
fits best. (I would have gone with
microsoft.public.excel.worksheet.functions--one you didn't post to.

And I don't recall ever seeing microsoft.public.excel.functions on the
MSServers. I have no idea if it exists on other servers, though.


stef wrote:
>
> hi dave,
> tx 4 picking this up. i'm desperate.... it must be making me stupid
> and ignorant as well.......
> yes, i'm sure.
> i am looking for a match of a cell in column a (or b or c--so A2 or B2
> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
> i want the value in column e of lookup range/table array to be returned
> in cell where i put vlookup formula.........
>
> similar to =VLOOKUP($B3,table_array,3,FALSE)
>
> what am i doing wrong?
>
> PS: incidentally, i do not understand why ur reply went to both
> newsgroups microsoft.public.excel and microsoft.public.excel.functions
> as i had set up the post to only reply to or follow-up to
> microsoft.public.excel. is it me or did u add urself the
> microsoft.public.excel.functions to the reply? (i'm just trying to find
> out as i do not want to multi post, etc....)
>
> Dave Peterson wrote:
> > You sure
> > =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> > shouldn't be:
> > =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
> >
> > (so the key looks to match column C).
> >
> >
> >
> > stef wrote:
> >> Excel 2002 SP3
> >> Win XP HE SP1
> >>
> >> *Follow-up group is: microsoft.public.excel* *only reply to:
> >> microsoft.public.excel*
> >>
> >> hi,
> >> i am having a problem with vlookup wherein if i use formula A:
> >> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
> >> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> >> the only difference is the table array range which in the formula B
> >> looks into the same sheet etc., but at more columns.
> >> i can't understand what is causing the problem.
> >> i believe that the columns do not need to be sorted (help file); the
> >> sheets are formatted properly, etc.
> >> i've done LOTS of vlookups in really complex spreadsheets in the past
> >> but am rusty right now and can't put my finger on what i am doing wrong.
> >> anyone can help?
> >

--

Dave Peterson

re. formula

no i dt think so dave. i mean i have right now a HUGE spreadsheet w/
the vlookup formula
=IF(ISERROR(VLOOKUP($B3,table_array,3,FALSE)),0,$D3)and that works great.

i can't understand why it is not working in my present case

the index formulas do not work....

what in the world am i doing wrong here? can u put ur finger on it??

re. the newsgroups: my mistake about the name--it is what u have said
"microsoft.public.excel.worksheet.functions".

re. cross posting, i usually set-up the original post to whatever (few)
newsgroups I need (as applicable--it can be tough s'times to distinguish
the exact difference between excel and excel.misc and
excel.worksheet.functions and how often the experts look into which
group, etc.) and set-up in the "To", etc. the follow-up group so that
when people hit "reply" it will automatically only reply to the
follow-up group.
AND i make sure to say at the top of the post which is the follow-up
group so that in the other newsgroup, even tho it looks like it's not
replied, one sd be able to see the *follow-up to: xxxxxx.newsgroup* and
understand the replies are there.
i guess i use this as my source:
http://www.imilly.com/noregrets.htm#guide see "cross-posting".

Dave Peterson wrote:
> I didn't read the original message--just the 2nd followup.
>
> It kind of looks like you want formulas like:
>
> =index(sheet2!a:a,match(c1,sheet2!c:c,0))
> =index(sheet2!b:b,match(c1,sheet2!c:c,0))
> =index(sheet2!d:d,match(c1,sheet2!c:c,0))
> =index(sheet2!e:e,match(c1,sheet2!c:c,0))
>
> The key is in column C of both sheets and you want to return the value in
> columns a, b, d, e when there's a match.
>
> Debra also has notes:
> http://www.contextures.com/xlFunctions03.html (for =index(match()))
>
> I didn't see any followup settings when I looked at the headers for the message
> I replied to. But that seems ok to me. If you cross post to a couple of
> newsgroups, the newsgroup that doesn't get the responses will look like the
> question still needs to be answered.
>
> In my opinion, you don't even need to cross post. Just pick the newsgroup that
> fits best. (I would have gone with
> microsoft.public.excel.worksheet.functions--one you didn't post to.
>
> And I don't recall ever seeing microsoft.public.excel.functions on the
> MSServers. I have no idea if it exists on other servers, though.
>
>
> stef wrote:
>> hi dave,
>> tx 4 picking this up. i'm desperate.... it must be making me stupid
>> and ignorant as well.......
>> yes, i'm sure.
>> i am looking for a match of a cell in column a (or b or c--so A2 or B2
>> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
>> i want the value in column e of lookup range/table array to be returned
>> in cell where i put vlookup formula.........
>>
>> similar to =VLOOKUP($B3,table_array,3,FALSE)
>>
>> what am i doing wrong?
>>
>> PS: incidentally, i do not understand why ur reply went to both
>> newsgroups microsoft.public.excel and microsoft.public.excel.functions
>> as i had set up the post to only reply to or follow-up to
>> microsoft.public.excel. is it me or did u add urself the
>> microsoft.public.excel.functions to the reply? (i'm just trying to find
>> out as i do not want to multi post, etc....)
>>
>> Dave Peterson wrote:
>>> You sure
>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>> shouldn't be:
>>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
>>>
>>> (so the key looks to match column C).
>>>
>>>
>>>
>>> stef wrote:
>>>> Excel 2002 SP3
>>>> Win XP HE SP1
>>>>
>>>> *Follow-up group is: microsoft.public.excel* *only reply to:
>>>> microsoft.public.excel*
>>>>
>>>> hi,
>>>> i am having a problem with vlookup wherein if i use formula A:
>>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>> the only difference is the table array range which in the formula B
>>>> looks into the same sheet etc., but at more columns.
>>>> i can't understand what is causing the problem.
>>>> i believe that the columns do not need to be sorted (help file); the
>>>> sheets are formatted properly, etc.
>>>> i've done LOTS of vlookups in really complex spreadsheets in the past
>>>> but am rusty right now and can't put my finger on what i am doing wrong.
>>>> anyone can help?
>

I read the original message a few times and I don't understand what you're
trying to do.

Maybe it's time for another attempt at an explanation.

=====
But if I reply in group "A" and it shows up in a different newsgroup "B", then
the person reading posts in group A don't know that you have replies. I don't
think that helps anyone who's reading group A posts.

These suggestions seem to be more appropriate to the *Excel* newsgroups on the
MSNewsservers:
http://www.cpearson.com/excel/newposte.htm



stef wrote:
>
> re. formula
>
> no i dt think so dave. i mean i have right now a HUGE spreadsheet w/
> the vlookup formula
> =IF(ISERROR(VLOOKUP($B3,table_array,3,FALSE)),0,$D3)and that works great.
>
> i can't understand why it is not working in my present case
>
> the index formulas do not work....
>
> what in the world am i doing wrong here? can u put ur finger on it??
>
> re. the newsgroups: my mistake about the name--it is what u have said
> "microsoft.public.excel.worksheet.functions".
>
> re. cross posting, i usually set-up the original post to whatever (few)
> newsgroups I need (as applicable--it can be tough s'times to distinguish
> the exact difference between excel and excel.misc and
> excel.worksheet.functions and how often the experts look into which
> group, etc.) and set-up in the "To", etc. the follow-up group so that
> when people hit "reply" it will automatically only reply to the
> follow-up group.
> AND i make sure to say at the top of the post which is the follow-up
> group so that in the other newsgroup, even tho it looks like it's not
> replied, one sd be able to see the *follow-up to: xxxxxx.newsgroup* and
> understand the replies are there.
> i guess i use this as my source:
> http://www.imilly.com/noregrets.htm#guide see "cross-posting".
>
> Dave Peterson wrote:
> > I didn't read the original message--just the 2nd followup.
> >
> > It kind of looks like you want formulas like:
> >
> > =index(sheet2!a:a,match(c1,sheet2!c:c,0))
> > =index(sheet2!b:b,match(c1,sheet2!c:c,0))
> > =index(sheet2!d:d,match(c1,sheet2!c:c,0))
> > =index(sheet2!e:e,match(c1,sheet2!c:c,0))
> >
> > The key is in column C of both sheets and you want to return the value in
> > columns a, b, d, e when there's a match.
> >
> > Debra also has notes:
> > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> >
> > I didn't see any followup settings when I looked at the headers for the message
> > I replied to. But that seems ok to me. If you cross post to a couple of
> > newsgroups, the newsgroup that doesn't get the responses will look like the
> > question still needs to be answered.
> >
> > In my opinion, you don't even need to cross post. Just pick the newsgroup that
> > fits best. (I would have gone with
> > microsoft.public.excel.worksheet.functions--one you didn't post to.
> >
> > And I don't recall ever seeing microsoft.public.excel.functions on the
> > MSServers. I have no idea if it exists on other servers, though.
> >
> >
> > stef wrote:
> >> hi dave,
> >> tx 4 picking this up. i'm desperate.... it must be making me stupid
> >> and ignorant as well.......
> >> yes, i'm sure.
> >> i am looking for a match of a cell in column a (or b or c--so A2 or B2
> >> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
> >> i want the value in column e of lookup range/table array to be returned
> >> in cell where i put vlookup formula.........
> >>
> >> similar to =VLOOKUP($B3,table_array,3,FALSE)
> >>
> >> what am i doing wrong?
> >>
> >> PS: incidentally, i do not understand why ur reply went to both
> >> newsgroups microsoft.public.excel and microsoft.public.excel.functions
> >> as i had set up the post to only reply to or follow-up to
> >> microsoft.public.excel. is it me or did u add urself the
> >> microsoft.public.excel.functions to the reply? (i'm just trying to find
> >> out as i do not want to multi post, etc....)
> >>
> >> Dave Peterson wrote:
> >>> You sure
> >>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> >>> shouldn't be:
> >>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
> >>>
> >>> (so the key looks to match column C).
> >>>
> >>>
> >>>
> >>> stef wrote:
> >>>> Excel 2002 SP3
> >>>> Win XP HE SP1
> >>>>
> >>>> *Follow-up group is: microsoft.public.excel* *only reply to:
> >>>> microsoft.public.excel*
> >>>>
> >>>> hi,
> >>>> i am having a problem with vlookup wherein if i use formula A:
> >>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
> >>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> >>>> the only difference is the table array range which in the formula B
> >>>> looks into the same sheet etc., but at more columns.
> >>>> i can't understand what is causing the problem.
> >>>> i believe that the columns do not need to be sorted (help file); the
> >>>> sheets are formatted properly, etc.
> >>>> i've done LOTS of vlookups in really complex spreadsheets in the past
> >>>> but am rusty right now and can't put my finger on what i am doing wrong.
> >>>> anyone can help?
> >

--

Dave Peterson

dave,
here's a thought.
i think it cd be because in my present case the key in the table_array
lookup is NOT leftmost but in the middle or to the right.... so maybe i
need to use a different formula (as u have mentioned........)

Dave Peterson wrote:
> I didn't read the original message--just the 2nd followup.
>
> It kind of looks like you want formulas like:
>
> =index(sheet2!a:a,match(c1,sheet2!c:c,0))
> =index(sheet2!b:b,match(c1,sheet2!c:c,0))
> =index(sheet2!d:d,match(c1,sheet2!c:c,0))
> =index(sheet2!e:e,match(c1,sheet2!c:c,0))
>
> The key is in column C of both sheets and you want to return the value in
> columns a, b, d, e when there's a match.
>
> Debra also has notes:
> http://www.contextures.com/xlFunctions03.html (for =index(match()))
>
> I didn't see any followup settings when I looked at the headers for the message
> I replied to. But that seems ok to me. If you cross post to a couple of
> newsgroups, the newsgroup that doesn't get the responses will look like the
> question still needs to be answered.
>
> In my opinion, you don't even need to cross post. Just pick the newsgroup that
> fits best. (I would have gone with
> microsoft.public.excel.worksheet.functions--one you didn't post to.
>
> And I don't recall ever seeing microsoft.public.excel.functions on the
> MSServers. I have no idea if it exists on other servers, though.
>
>
> stef wrote:
>> hi dave,
>> tx 4 picking this up. i'm desperate.... it must be making me stupid
>> and ignorant as well.......
>> yes, i'm sure.
>> i am looking for a match of a cell in column a (or b or c--so A2 or B2
>> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
>> i want the value in column e of lookup range/table array to be returned
>> in cell where i put vlookup formula.........
>>
>> similar to =VLOOKUP($B3,table_array,3,FALSE)
>>
>> what am i doing wrong?
>>
>> PS: incidentally, i do not understand why ur reply went to both
>> newsgroups microsoft.public.excel and microsoft.public.excel.functions
>> as i had set up the post to only reply to or follow-up to
>> microsoft.public.excel. is it me or did u add urself the
>> microsoft.public.excel.functions to the reply? (i'm just trying to find
>> out as i do not want to multi post, etc....)
>>
>> Dave Peterson wrote:
>>> You sure
>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>> shouldn't be:
>>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
>>>
>>> (so the key looks to match column C).
>>>
>>>
>>>
>>> stef wrote:
>>>> Excel 2002 SP3
>>>> Win XP HE SP1
>>>>
>>>> *Follow-up group is: microsoft.public.excel* *only reply to:
>>>> microsoft.public.excel*
>>>>
>>>> hi,
>>>> i am having a problem with vlookup wherein if i use formula A:
>>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>> the only difference is the table array range which in the formula B
>>>> looks into the same sheet etc., but at more columns.
>>>> i can't understand what is causing the problem.
>>>> i believe that the columns do not need to be sorted (help file); the
>>>> sheets are formatted properly, etc.
>>>> i've done LOTS of vlookups in really complex spreadsheets in the past
>>>> but am rusty right now and can't put my finger on what i am doing wrong.
>>>> anyone can help?
>

1st: tx 4 ur patience
2nd: forget about the newsgroup thing for now. it's enough to struggle
w/ the issue at hand right now
3:

In sheet1, I have:
A1 joe
A2 bill
A3 jack
A4 george
In sheet 2 I have:
A1 joe B1 peterson C1 550 main street D1 chicago E1 IL
A2 bill B2 johnson D2 500 main street D2 new york E2 NY
etc...............

In cell sheet1 B1, i want to lookup "joe" (sheet1 A1) and IF "joe"
appears in an array in sheet 2 (array could be sheet2 $A$1 to $E$500), I
want sheet1 B2 to give me the value in sheet2 E100 [assuming "joe"
appears in row 100 in sheet2], etc. (or any other column value I would
choose [address or state] in row 100 where "joe" appears.

etc.......




Dave Peterson wrote:
> I read the original message a few times and I don't understand what you're
> trying to do.
>
> Maybe it's time for another attempt at an explanation.
>
> =====
> But if I reply in group "A" and it shows up in a different newsgroup "B", then
> the person reading posts in group A don't know that you have replies. I don't
> think that helps anyone who's reading group A posts.
>
> These suggestions seem to be more appropriate to the *Excel* newsgroups on the
> MSNewsservers:
> http://www.cpearson.com/excel/newposte.htm
>
>
>
> stef wrote:
>> re. formula
>>
>> no i dt think so dave. i mean i have right now a HUGE spreadsheet w/
>> the vlookup formula
>> =IF(ISERROR(VLOOKUP($B3,table_array,3,FALSE)),0,$D3)and that works great.
>>
>> i can't understand why it is not working in my present case
>>
>> the index formulas do not work....
>>
>> what in the world am i doing wrong here? can u put ur finger on it??
>>
>> re. the newsgroups: my mistake about the name--it is what u have said
>> "microsoft.public.excel.worksheet.functions".
>>
>> re. cross posting, i usually set-up the original post to whatever (few)
>> newsgroups I need (as applicable--it can be tough s'times to distinguish
>> the exact difference between excel and excel.misc and
>> excel.worksheet.functions and how often the experts look into which
>> group, etc.) and set-up in the "To", etc. the follow-up group so that
>> when people hit "reply" it will automatically only reply to the
>> follow-up group.
>> AND i make sure to say at the top of the post which is the follow-up
>> group so that in the other newsgroup, even tho it looks like it's not
>> replied, one sd be able to see the *follow-up to: xxxxxx.newsgroup* and
>> understand the replies are there.
>> i guess i use this as my source:
>> http://www.imilly.com/noregrets.htm#guide see "cross-posting".
>>
>> Dave Peterson wrote:
>>> I didn't read the original message--just the 2nd followup.
>>>
>>> It kind of looks like you want formulas like:
>>>
>>> =index(sheet2!a:a,match(c1,sheet2!c:c,0))
>>> =index(sheet2!b:b,match(c1,sheet2!c:c,0))
>>> =index(sheet2!d:d,match(c1,sheet2!c:c,0))
>>> =index(sheet2!e:e,match(c1,sheet2!c:c,0))
>>>
>>> The key is in column C of both sheets and you want to return the value in
>>> columns a, b, d, e when there's a match.
>>>
>>> Debra also has notes:
>>> http://www.contextures.com/xlFunctions03.html (for =index(match()))
>>>
>>> I didn't see any followup settings when I looked at the headers for the message
>>> I replied to. But that seems ok to me. If you cross post to a couple of
>>> newsgroups, the newsgroup that doesn't get the responses will look like the
>>> question still needs to be answered.
>>>
>>> In my opinion, you don't even need to cross post. Just pick the newsgroup that
>>> fits best. (I would have gone with
>>> microsoft.public.excel.worksheet.functions--one you didn't post to.
>>>
>>> And I don't recall ever seeing microsoft.public.excel.functions on the
>>> MSServers. I have no idea if it exists on other servers, though.
>>>
>>>
>>> stef wrote:
>>>> hi dave,
>>>> tx 4 picking this up. i'm desperate.... it must be making me stupid
>>>> and ignorant as well.......
>>>> yes, i'm sure.
>>>> i am looking for a match of a cell in column a (or b or c--so A2 or B2
>>>> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
>>>> i want the value in column e of lookup range/table array to be returned
>>>> in cell where i put vlookup formula.........
>>>>
>>>> similar to =VLOOKUP($B3,table_array,3,FALSE)
>>>>
>>>> what am i doing wrong?
>>>>
>>>> PS: incidentally, i do not understand why ur reply went to both
>>>> newsgroups microsoft.public.excel and microsoft.public.excel.functions
>>>> as i had set up the post to only reply to or follow-up to
>>>> microsoft.public.excel. is it me or did u add urself the
>>>> microsoft.public.excel.functions to the reply? (i'm just trying to find
>>>> out as i do not want to multi post, etc....)
>>>>
>>>> Dave Peterson wrote:
>>>>> You sure
>>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>>> shouldn't be:
>>>>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
>>>>>
>>>>> (so the key looks to match column C).
>>>>>
>>>>>
>>>>>
>>>>> stef wrote:
>>>>>> Excel 2002 SP3
>>>>>> Win XP HE SP1
>>>>>>
>>>>>> *Follow-up group is: microsoft.public.excel* *only reply to:
>>>>>> microsoft.public.excel*
>>>>>>
>>>>>> hi,
>>>>>> i am having a problem with vlookup wherein if i use formula A:
>>>>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
>>>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>>>> the only difference is the table array range which in the formula B
>>>>>> looks into the same sheet etc., but at more columns.
>>>>>> i can't understand what is causing the problem.
>>>>>> i believe that the columns do not need to be sorted (help file); the
>>>>>> sheets are formatted properly, etc.
>>>>>> i've done LOTS of vlookups in really complex spreadsheets in the past
>>>>>> but am rusty right now and can't put my finger on what i am doing wrong.
>>>>>> anyone can help?
>

You can make a giant formula to check each column, but I'd use some helper cells
to hold some of the work--so it doesn't have to be duplicated within the
formula:

I'd put this in B1:
=match(a1,sheet2!a:a,0)

In C1:
=match(a1,sheet2!b:b,0)

In D1:
=match(a1,sheet2!c:c,0)

In E1:
=match(a1,sheet2!d:d,0)

I figured that you didn't mean to include column E in the search, since that's
the column you want to bring back.

Then put this in F1:
=MIN(IF(ISNUMBER(B1:E1),B1:E1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This goes in G1:
=IF(F1=0,"No match",INDEX(Sheet2!E:E,F1,0))

If you want to retrieve a value from a different column, change sheet2!e:e to
what you want.

stef wrote:
>
> 1st: tx 4 ur patience
> 2nd: forget about the newsgroup thing for now. it's enough to struggle
> w/ the issue at hand right now
> 3:
>
> In sheet1, I have:
> A1 joe
> A2 bill
> A3 jack
> A4 george
> In sheet 2 I have:
> A1 joe B1 peterson C1 550 main street D1 chicago E1 IL
> A2 bill B2 johnson D2 500 main street D2 new york E2 NY
> etc...............
>
> In cell sheet1 B1, i want to lookup "joe" (sheet1 A1) and IF "joe"
> appears in an array in sheet 2 (array could be sheet2 $A$1 to $E$500), I
> want sheet1 B2 to give me the value in sheet2 E100 [assuming "joe"
> appears in row 100 in sheet2], etc. (or any other column value I would
> choose [address or state] in row 100 where "joe" appears.
>
> etc.......
>
> Dave Peterson wrote:
> > I read the original message a few times and I don't understand what you're
> > trying to do.
> >
> > Maybe it's time for another attempt at an explanation.
> >
> > =====
> > But if I reply in group "A" and it shows up in a different newsgroup "B", then
> > the person reading posts in group A don't know that you have replies. I don't
> > think that helps anyone who's reading group A posts.
> >
> > These suggestions seem to be more appropriate to the *Excel* newsgroups on the
> > MSNewsservers:
> > http://www.cpearson.com/excel/newposte.htm
> >
> >
> >
> > stef wrote:
> >> re. formula
> >>
> >> no i dt think so dave. i mean i have right now a HUGE spreadsheet w/
> >> the vlookup formula
> >> =IF(ISERROR(VLOOKUP($B3,table_array,3,FALSE)),0,$D3)and that works great.
> >>
> >> i can't understand why it is not working in my present case
> >>
> >> the index formulas do not work....
> >>
> >> what in the world am i doing wrong here? can u put ur finger on it??
> >>
> >> re. the newsgroups: my mistake about the name--it is what u have said
> >> "microsoft.public.excel.worksheet.functions".
> >>
> >> re. cross posting, i usually set-up the original post to whatever (few)
> >> newsgroups I need (as applicable--it can be tough s'times to distinguish
> >> the exact difference between excel and excel.misc and
> >> excel.worksheet.functions and how often the experts look into which
> >> group, etc.) and set-up in the "To", etc. the follow-up group so that
> >> when people hit "reply" it will automatically only reply to the
> >> follow-up group.
> >> AND i make sure to say at the top of the post which is the follow-up
> >> group so that in the other newsgroup, even tho it looks like it's not
> >> replied, one sd be able to see the *follow-up to: xxxxxx.newsgroup* and
> >> understand the replies are there.
> >> i guess i use this as my source:
> >> http://www.imilly.com/noregrets.htm#guide see "cross-posting".
> >>
> >> Dave Peterson wrote:
> >>> I didn't read the original message--just the 2nd followup.
> >>>
> >>> It kind of looks like you want formulas like:
> >>>
> >>> =index(sheet2!a:a,match(c1,sheet2!c:c,0))
> >>> =index(sheet2!b:b,match(c1,sheet2!c:c,0))
> >>> =index(sheet2!d:d,match(c1,sheet2!c:c,0))
> >>> =index(sheet2!e:e,match(c1,sheet2!c:c,0))
> >>>
> >>> The key is in column C of both sheets and you want to return the value in
> >>> columns a, b, d, e when there's a match.
> >>>
> >>> Debra also has notes:
> >>> http://www.contextures.com/xlFunctions03.html (for =index(match()))
> >>>
> >>> I didn't see any followup settings when I looked at the headers for the message
> >>> I replied to. But that seems ok to me. If you cross post to a couple of
> >>> newsgroups, the newsgroup that doesn't get the responses will look like the
> >>> question still needs to be answered.
> >>>
> >>> In my opinion, you don't even need to cross post. Just pick the newsgroup that
> >>> fits best. (I would have gone with
> >>> microsoft.public.excel.worksheet.functions--one you didn't post to.
> >>>
> >>> And I don't recall ever seeing microsoft.public.excel.functions on the
> >>> MSServers. I have no idea if it exists on other servers, though.
> >>>
> >>>
> >>> stef wrote:
> >>>> hi dave,
> >>>> tx 4 picking this up. i'm desperate.... it must be making me stupid
> >>>> and ignorant as well.......
> >>>> yes, i'm sure.
> >>>> i am looking for a match of a cell in column a (or b or c--so A2 or B2
> >>>> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
> >>>> i want the value in column e of lookup range/table array to be returned
> >>>> in cell where i put vlookup formula.........
> >>>>
> >>>> similar to =VLOOKUP($B3,table_array,3,FALSE)
> >>>>
> >>>> what am i doing wrong?
> >>>>
> >>>> PS: incidentally, i do not understand why ur reply went to both
> >>>> newsgroups microsoft.public.excel and microsoft.public.excel.functions
> >>>> as i had set up the post to only reply to or follow-up to
> >>>> microsoft.public.excel. is it me or did u add urself the
> >>>> microsoft.public.excel.functions to the reply? (i'm just trying to find
> >>>> out as i do not want to multi post, etc....)
> >>>>
> >>>> Dave Peterson wrote:
> >>>>> You sure
> >>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> >>>>> shouldn't be:
> >>>>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
> >>>>>
> >>>>> (so the key looks to match column C).
> >>>>>
> >>>>>
> >>>>>
> >>>>> stef wrote:
> >>>>>> Excel 2002 SP3
> >>>>>> Win XP HE SP1
> >>>>>>
> >>>>>> *Follow-up group is: microsoft.public.excel* *only reply to:
> >>>>>> microsoft.public.excel*
> >>>>>>
> >>>>>> hi,
> >>>>>> i am having a problem with vlookup wherein if i use formula A:
> >>>>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
> >>>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
> >>>>>> the only difference is the table array range which in the formula B
> >>>>>> looks into the same sheet etc., but at more columns.
> >>>>>> i can't understand what is causing the problem.
> >>>>>> i believe that the columns do not need to be sorted (help file); the
> >>>>>> sheets are formatted properly, etc.
> >>>>>> i've done LOTS of vlookups in really complex spreadsheets in the past
> >>>>>> but am rusty right now and can't put my finger on what i am doing wrong.
> >>>>>> anyone can help?
> >

--

Dave Peterson

let me put it in the sheet and see what happens.
i'll be back to post results......

Dave Peterson wrote:
> You can make a giant formula to check each column, but I'd use some helper cells
> to hold some of the work--so it doesn't have to be duplicated within the
> formula:
>
> I'd put this in B1:
> =match(a1,sheet2!a:a,0)
>
> In C1:
> =match(a1,sheet2!b:b,0)
>
> In D1:
> =match(a1,sheet2!c:c,0)
>
> In E1:
> =match(a1,sheet2!d:d,0)
>
> I figured that you didn't mean to include column E in the search, since that's
> the column you want to bring back.
>
> Then put this in F1:
> =MIN(IF(ISNUMBER(B1:E1),B1:E1))
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> This goes in G1:
> =IF(F1=0,"No match",INDEX(Sheet2!E:E,F1,0))
>
> If you want to retrieve a value from a different column, change sheet2!e:e to
> what you want.
>
> stef wrote:
>> 1st: tx 4 ur patience
>> 2nd: forget about the newsgroup thing for now. it's enough to struggle
>> w/ the issue at hand right now
>> 3:
>>
>> In sheet1, I have:
>> A1 joe
>> A2 bill
>> A3 jack
>> A4 george
>> In sheet 2 I have:
>> A1 joe B1 peterson C1 550 main street D1 chicago E1 IL
>> A2 bill B2 johnson D2 500 main street D2 new york E2 NY
>> etc...............
>>
>> In cell sheet1 B1, i want to lookup "joe" (sheet1 A1) and IF "joe"
>> appears in an array in sheet 2 (array could be sheet2 $A$1 to $E$500), I
>> want sheet1 B2 to give me the value in sheet2 E100 [assuming "joe"
>> appears in row 100 in sheet2], etc. (or any other column value I would
>> choose [address or state] in row 100 where "joe" appears.
>>
>> etc.......
>>
>> Dave Peterson wrote:
>>> I read the original message a few times and I don't understand what you're
>>> trying to do.
>>>
>>> Maybe it's time for another attempt at an explanation.
>>>
>>> =====
>>> But if I reply in group "A" and it shows up in a different newsgroup "B", then
>>> the person reading posts in group A don't know that you have replies. I don't
>>> think that helps anyone who's reading group A posts.
>>>
>>> These suggestions seem to be more appropriate to the *Excel* newsgroups on the
>>> MSNewsservers:
>>> http://www.cpearson.com/excel/newposte.htm
>>>
>>>
>>>
>>> stef wrote:
>>>> re. formula
>>>>
>>>> no i dt think so dave. i mean i have right now a HUGE spreadsheet w/
>>>> the vlookup formula
>>>> =IF(ISERROR(VLOOKUP($B3,table_array,3,FALSE)),0,$D3)and that works great.
>>>>
>>>> i can't understand why it is not working in my present case
>>>>
>>>> the index formulas do not work....
>>>>
>>>> what in the world am i doing wrong here? can u put ur finger on it??
>>>>
>>>> re. the newsgroups: my mistake about the name--it is what u have said
>>>> "microsoft.public.excel.worksheet.functions".
>>>>
>>>> re. cross posting, i usually set-up the original post to whatever (few)
>>>> newsgroups I need (as applicable--it can be tough s'times to distinguish
>>>> the exact difference between excel and excel.misc and
>>>> excel.worksheet.functions and how often the experts look into which
>>>> group, etc.) and set-up in the "To", etc. the follow-up group so that
>>>> when people hit "reply" it will automatically only reply to the
>>>> follow-up group.
>>>> AND i make sure to say at the top of the post which is the follow-up
>>>> group so that in the other newsgroup, even tho it looks like it's not
>>>> replied, one sd be able to see the *follow-up to: xxxxxx.newsgroup* and
>>>> understand the replies are there.
>>>> i guess i use this as my source:
>>>> http://www.imilly.com/noregrets.htm#guide see "cross-posting".
>>>>
>>>> Dave Peterson wrote:
>>>>> I didn't read the original message--just the 2nd followup.
>>>>>
>>>>> It kind of looks like you want formulas like:
>>>>>
>>>>> =index(sheet2!a:a,match(c1,sheet2!c:c,0))
>>>>> =index(sheet2!b:b,match(c1,sheet2!c:c,0))
>>>>> =index(sheet2!d:d,match(c1,sheet2!c:c,0))
>>>>> =index(sheet2!e:e,match(c1,sheet2!c:c,0))
>>>>>
>>>>> The key is in column C of both sheets and you want to return the value in
>>>>> columns a, b, d, e when there's a match.
>>>>>
>>>>> Debra also has notes:
>>>>> http://www.contextures.com/xlFunctions03.html (for =index(match()))
>>>>>
>>>>> I didn't see any followup settings when I looked at the headers for the message
>>>>> I replied to. But that seems ok to me. If you cross post to a couple of
>>>>> newsgroups, the newsgroup that doesn't get the responses will look like the
>>>>> question still needs to be answered.
>>>>>
>>>>> In my opinion, you don't even need to cross post. Just pick the newsgroup that
>>>>> fits best. (I would have gone with
>>>>> microsoft.public.excel.worksheet.functions--one you didn't post to.
>>>>>
>>>>> And I don't recall ever seeing microsoft.public.excel.functions on the
>>>>> MSServers. I have no idea if it exists on other servers, though.
>>>>>
>>>>>
>>>>> stef wrote:
>>>>>> hi dave,
>>>>>> tx 4 picking this up. i'm desperate.... it must be making me stupid
>>>>>> and ignorant as well.......
>>>>>> yes, i'm sure.
>>>>>> i am looking for a match of a cell in column a (or b or c--so A2 or B2
>>>>>> or C2 doesn't matter) in table A$2:$E$48 and then, if there is a match,
>>>>>> i want the value in column e of lookup range/table array to be returned
>>>>>> in cell where i put vlookup formula.........
>>>>>>
>>>>>> similar to =VLOOKUP($B3,table_array,3,FALSE)
>>>>>>
>>>>>> what am i doing wrong?
>>>>>>
>>>>>> PS: incidentally, i do not understand why ur reply went to both
>>>>>> newsgroups microsoft.public.excel and microsoft.public.excel.functions
>>>>>> as i had set up the post to only reply to or follow-up to
>>>>>> microsoft.public.excel. is it me or did u add urself the
>>>>>> microsoft.public.excel.functions to the reply? (i'm just trying to find
>>>>>> out as i do not want to multi post, etc....)
>>>>>>
>>>>>> Dave Peterson wrote:
>>>>>>> You sure
>>>>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>>>>> shouldn't be:
>>>>>>> =VLOOKUP(A2,NJ!$c$2:$E$48,3,FALSE)
>>>>>>>
>>>>>>> (so the key looks to match column C).
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> stef wrote:
>>>>>>>> Excel 2002 SP3
>>>>>>>> Win XP HE SP1
>>>>>>>>
>>>>>>>> *Follow-up group is: microsoft.public.excel* *only reply to:
>>>>>>>> microsoft.public.excel*
>>>>>>>>
>>>>>>>> hi,
>>>>>>>> i am having a problem with vlookup wherein if i use formula A:
>>>>>>>> =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't:
>>>>>>>> =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE)
>>>>>>>> the only difference is the table array range which in the formula B
>>>>>>>> looks into the same sheet etc., but at more columns.
>>>>>>>> i can't understand what is causing the problem.
>>>>>>>> i believe that the columns do not need to be sorted (help file); the
>>>>>>>> sheets are formatted properly, etc.
>>>>>>>> i've done LOTS of vlookups in really complex spreadsheets in the past
>>>>>>>> but am rusty right now and can't put my finger on what i am doing wrong.
>>>>>>>> anyone can help?
>