| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to auto-return to next cell that accepts data. | Excel Worksheet Functions | |||
| Using cell as worksheet name to return data on that sheet | Excel Worksheet Functions | |||
| return a portion of data entered into a cell | Excel Discussion (Misc queries) | |||
| Return Data from Specified Cell Series | Excel Discussion (Misc queries) | |||
| How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
Linear Mode
