If there is, it's beyond me.
It wouldn't take too much for a UDF, though. If that's ok, what version of
excel are you using? It would make a difference in the code.
stef wrote:
dave, i know ur an expert so i am getting worried now. is what i',m
asking that complex? i mean it really shouldn't be.......
there must be some kind of index+match formula i can use to accomplish
this.....
Dave Peterson wrote:
I don't have any other suggestions.
stef wrote:
dave,
it works but it's too much of of workaround for me to be able to use it
in really large spreadsheets with different structures, etc.
what I really need is some kind of lookup basically where i look up
"joe" in a table array (such as Sheet2!$A$1:$$Z$100 or whatever name I
define) and it returns the cell value in the specified column that
matches the row where "joe" is in the table array being looked up.......
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?
