| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confused
Hi there.
I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confused
One way:
=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confus
Hi, That worked beautifully! Thank you so much. This is what worked for me.
=SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confus
1. That's how SEARCH handles empty cells. You could use an IF test to see if
the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confus
Thank you so much for your help.
I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confus
=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confused
no problem the simple way would be =VLOOKUP formula think of the second sheet as raw data that will only to popluate the next cell with the corresponding data. in sheet1 Column C Column D Contains value Retrieves the value for the second sheet the forlmual in column would be =VLOOKUP(C1,Sheet2!$b$1:$C$3,2,FALSE) I know it looks scary but its not. VLOOKUP is the formula. C1 is were the data is coming form. Sheet2!$b$1:$C$3 is called a table array The next (2) is the column of data form the table array you would like to see False mean it will pull the exact text sting ascending order does not matter "grateful" wrote: Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I think I need to use Index, Match, and IsNumber but am confus
Hi Dylan, Thank you for your response....I think you're right that VLookup
should work somehow...I've come across it when I was trying to figure it out...even though I couldn't get it to work... I don't really understand it. I tried your formula and it comes back #N/A....and I also tried it this way: =VLOOKUP(C7,'[Workbook2.xls]Current'!$B$1:$C$146,2,FALSE) for example in Row 7, but it still comes back #N/A...so maybe I don't understand how the array thing works.... Thanks a lot. If you have time....I wouldn't mind understanding VLookUp, but if you don't I understand. Thanks! "Dylan @ UAFC" wrote: no problem the simple way would be =VLOOKUP formula think of the second sheet as raw data that will only to popluate the next cell with the corresponding data. in sheet1 Column C Column D Contains value Retrieves the value for the second sheet the forlmual in column would be =VLOOKUP(C1,Sheet2!$b$1:$C$3,2,FALSE) I know it looks scary but its not. VLOOKUP is the formula. C1 is were the data is coming form. Sheet2!$b$1:$C$3 is called a table array The next (2) is the column of data form the table array you would like to see False mean it will pull the exact text sting ascending order does not matter "grateful" wrote: Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if(and(isnumber(match(... vs if(and(match(... | Excel Discussion (Misc queries) | |||
| Isnumber Match Index help request | Excel Discussion (Misc queries) | |||
| if, Isnumber, Match, &index query | Excel Discussion (Misc queries) | |||
| Match, Index, Vlookup, If (confused) | Excel Discussion (Misc queries) | |||
| problem with =isnumber(match(right(... | Excel Worksheet Functions | |||
Hybrid Mode
