I am hoping that you can help me with a formula
I want to look up data in one column and search for a specific value and
then return a value from the same row, several columns before.
I want to search for the number 1 in G7:G23
If 1 shows up in G18, I want to have the contents of A18 appear in the new
cell
Any suggestions?
|
|
conniefitzgerald wrote:
[Quoted Text] > I am hoping that you can help me with a formula
>
> I want to look up data in one column and search for a specific value and
> then return a value from the same row, several columns before.
>
> I want to search for the number 1 in G7:G23
> If 1 shows up in G18, I want to have the contents of A18 appear in the new
> cell
>
> Any suggestions?
Take a look here:
http://www.contextures.com/xlFunctions03.html#Match
|
|
conniefitzgerald wrote:
[Quoted Text] > I am hoping that you can help me with a formula
>
> I want to look up data in one column and search for a specific value and
> then return a value from the same row, several columns before.
>
> I want to search for the number 1 in G7:G23
> If 1 shows up in G18, I want to have the contents of A18 appear in the new
> cell
>
> Any suggestions?
Actually, look here (just a little farther down the page):
http://www.contextures.com/xlFunctions03.html#IndexMatch
|
|
|
|
|
[Quoted Text] >*=INDEX(A7:A23,MATCH(1,G7:G23,0),1)*
I would recommend that you not include the * in your replies. Some folks
will think those are part of the formula.
--
Biff
Microsoft Excel MVP
"JBeaucaire" <JBeaucaire.3lakap[ at ]thecodecage.com> wrote in message
news:JBeaucaire.3lakap[ at ]thecodecage.com...
>
> This should do the trick:
>
> *=INDEX(A7:A23,MATCH(1,G7:G23,0),1)*
>
>
> --
> JBeaucaire
> ------------------------------------------------------------------------
> JBeaucaire's Profile:
> http://www.thecodecage.com/forumz/member.php?userid=73
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45494
>
|
|
|
|
Hi JBeaucaire,
You did make the formula you posted bold though.
Many posters are using the newgroups via nntp rather than web portals.
Cheers
Andy
JBeaucaire wrote:
[Quoted Text] > I didn't put any asterisks in my reply...and I don't see them either.
> Are you guys using some other interface than the web portal that is
> changing the content displayed on my posts?
>
>
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
|
|
I'm using Outlook Express to access the groups. In OE the formula is
displayed as:
*=INDEX(A7:A23,MATCH(1,G7:G23,0),1)*
--
Biff
Microsoft Excel MVP
"JBeaucaire" <JBeaucaire.3lalkx[ at ]thecodecage.com> wrote in message
news:JBeaucaire.3lalkx[ at ]thecodecage.com...
[Quoted Text]
|
|
Hi,
I'm using the Web portal and this shows up with *'s
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"JBeaucaire" wrote:
[Quoted Text]
|
|
I see it the same way as Biff, viewed through Google Groups.
Pete
On Dec 31, 5:12 pm, "T. Valko" <biffinp...[ at ]comcast.net> wrote:
[Quoted Text] > I'm using Outlook Express to access the groups. In OE the formula is
> displayed as:
>
> *=INDEX(A7:A23,MATCH(1,G7:G23,0),1)*
>
> --
> Biff
> Microsoft Excel MVP
>
|
|
Hi,
You can use
=LOOKUP(1,G7:G23,A7:A23)
or
=OFFSET(A6,MATCH(1,G7:G23,0),)
or
=INDEX(A7:A23,MATCH(1,G7:G23,0))
or replace the 1 with a cell reference.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"conniefitzgerald" wrote:
[Quoted Text] > I am hoping that you can help me with a formula
>
> I want to look up data in one column and search for a specific value and
> then return a value from the same row, several columns before.
>
> I want to search for the number 1 in G7:G23
> If 1 shows up in G18, I want to have the contents of A18 appear in the new
> cell
>
> Any suggestions?
|
|
|
|
Well, thankfully I knew enough to not use the asterisks. It worked like a
charm. Thank you!
"JBeaucaire" wrote:
[Quoted Text]
|
|
You need to remember that usenet is a plain text medium, so playing around
with formatting characters is asking for trouble.
Using a web site to access newsgroups might occasionally have some merits,
such as Google's archive search, but in general a web site is not a sensible
way to access a newsgroup.
--
David Biddulph
JBeaucaire wrote:
[Quoted Text] > Well, isn't that cheeky? Using bold to standout the formula in a post
> causes additional formatting to be displayed to text view? That's
> patently absurd.
>
> OK. Good to know. I'll stop doing that on this web site. (mutter)
|
|