Return a cell from more than just one row of data
 

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

With your posted list in A1:E4

Try this:
F1: ( the letter couplet to find)

This formula returns the Task for that couplet
G1:
=IF(COUNTIF($C$1:$E$4,F1),INDEX($A$1:$A$4,SUMPRODU CT(($C$1:$E$4=F1)*ROW($C$1:$E$4))),"No Match")

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

where G1 contains the 2 letter code you are searching for, one way is:

=INDEX(A1:A4,SMALL(IF((C1:E4=G1),ROW(A1:A4)-MIN(ROW(A1:A4))+1),1))

array entered w/Cntrl+Shift+Enter (or you'll get #VALUE error).


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

I think I see how this could work, but since I am trying to get back text, I
am just getting an error(#VALUE!). Is there anything else I can do to get
back the text in the block I am searching for?

"Ron Coderre" wrote:

With your posted list in A1:E4

Try this:
F1: ( the letter couplet to find)

This formula returns the Task for that couplet
G1:
=IF(COUNTIF($C$1:$E$4,F1),INDEX($A$1:$A$4,SUMPRODU CT(($C$1:$E$4=F1)*ROW($C$1:$E$4))),"No Match")

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

I think you'll have to post your formula.....the one I posted is working fine
with your sample data.

***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I think I see how this could work, but since I am trying to get back text, I
am just getting an error(#VALUE!). Is there anything else I can do to get
back the text in the block I am searching for?

"Ron Coderre" wrote:

With your posted list in A1:E4

Try this:
F1: ( the letter couplet to find)

This formula returns the Task for that couplet
G1:
=IF(COUNTIF($C$1:$E$4,F1),INDEX($A$1:$A$4,SUMPRODU CT(($C$1:$E$4=F1)*ROW($C$1:$E$4))),"No Match")

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

Thank you very much, after looking at it again, I see where I made the
mistake. This works great.

"Ron Coderre" wrote:

I think you'll have to post your formula.....the one I posted is working fine
with your sample data.

***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I think I see how this could work, but since I am trying to get back text, I
am just getting an error(#VALUE!). Is there anything else I can do to get
back the text in the block I am searching for?

"Ron Coderre" wrote:

With your posted list in A1:E4

Try this:
F1: ( the letter couplet to find)

This formula returns the Task for that couplet
G1:
=IF(COUNTIF($C$1:$E$4,F1),INDEX($A$1:$A$4,SUMPRODU CT(($C$1:$E$4=F1)*ROW($C$1:$E$4))),"No Match")

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.

Return a cell from more than just one row of data
 

I'm so glad you got it to work for you.........and thanks for the feedback!

***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

Thank you very much, after looking at it again, I see where I made the
mistake. This works great.

"Ron Coderre" wrote:

I think you'll have to post your formula.....the one I posted is working fine
with your sample data.

***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I think I see how this could work, but since I am trying to get back text, I
am just getting an error(#VALUE!). Is there anything else I can do to get
back the text in the block I am searching for?

"Ron Coderre" wrote:

With your posted list in A1:E4

Try this:
F1: ( the letter couplet to find)

This formula returns the Task for that couplet
G1:
=IF(COUNTIF($C$1:$E$4,F1),INDEX($A$1:$A$4,SUMPRODU CT(($C$1:$E$4=F1)*ROW($C$1:$E$4))),"No Match")

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"lindsayhyle" wrote:

I have a spreadsheet that looks like this:

A B C D E
1 Task Name AA AL PX

2 Task Name BL CE DF

3 Task Name AE DV QW

4 Task Name AZ SR UV

I want to be able to make a list of the two letters in columns C, D, and E
and have it return the task in column A like this:
AA Name
AB Name
AL Name

and so on. Is this possible to do a search from multiple rows and columns
to return the task in column A? Thanks.