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:
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 he

http://www.contextures.com/xlFunctions03.html#Match

conniefitzgerald wrote:
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/xlFunctio...tml#IndexMatch

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/sh...ad.php?t=45494

*=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" wrote in message
...

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/sh...ad.php?t=45494

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?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.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:
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" wrote in message
...

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?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile:
http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45494

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:


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?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45494

I see it the same way as Biff, viewed through Google Groups.

Pete

On Dec 31, 5:12*pm, "T. Valko" wrote:
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:

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, 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)


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45494

Well, thankfully I knew enough to not use the asterisks. It worked like a
charm. Thank you!

"JBeaucaire" wrote:


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/sh...ad.php?t=45494

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:
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)