If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search Entire Sheet instead of 1?
I have a function whereby if a string in Sheet 2/Column1 is contained in
Sheet1/Column1, it returns Sheet2/Column1. I am doing this manually but would like it to step through the whole table in Sheet 2 (i.e. step through all for a match. Currently my formula looks like this; =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A3)), Sheet2!A3) To get it do do each manually of course I'd need to to =IF(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!A1)), Sheet2!A1, IF(ISNUMBER(SEARCH(Sheet2!B2,Sheet1!A1)), Sheet2!A2), =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A1)), Sheet2!A3), etc So I am trying to do the same thing manually. I have played with VLoopup and Index but am only frustrating myself |
#2
|
|||
|
|||
Search Entire Sheet instead of 1?
msnyc07 wrote:
I have a function whereby if a string in Sheet 2/Column1 is contained in Sheet1/Column1, it returns Sheet2/Column1. I am doing this manually but would like it to step through the whole table in Sheet 2 (i.e. step through all for a match. Currently my formula looks like this; =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A3)), Sheet2!A3) To get it do do each manually of course I'd need to to =IF(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!A1)), Sheet2!A1, IF(ISNUMBER(SEARCH(Sheet2!B2,Sheet1!A1)), Sheet2!A2), =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A1)), Sheet2!A3), etc So I am trying to do the same thing manually. I have played with VLoopup and Index but am only frustrating myself This is a little hard to follow, but probably not that hard to accomplish if you could show some sample data and expected results. Also, where you want to put this formula would be helpful. |
#3
|
|||
|
|||
Search Entire Sheet instead of 1?
You can use this array* formula. Note that the ranges inside the IF function
cannot callout entire column. =INDEX(Sheet1!B:B,MIN(IF(ISNUMBER(SEARCH(B3,Sheet1 !A1:A250)),ROW(Sheet1!A1:A250)))) if you want to be able to copy this down and get all the results: =INDEX(Sheet1!B:B,SMALL(IF(ISNUMBER(SEARCH(B$3,She et1!A$1:A$250)),ROW(Sheet1!A$1:A$250)),ROW(A1))) Now the small function will step through each result. *Arry formulas need to be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M "msnyc07" wrote in message ... I have a function whereby if a string in Sheet 2/Column1 is contained in Sheet1/Column1, it returns Sheet2/Column1. I am doing this manually but would like it to step through the whole table in Sheet 2 (i.e. step through all for a match. Currently my formula looks like this; =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A3)), Sheet2!A3) To get it do do each manually of course I'd need to to =IF(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!A1)), Sheet2!A1, IF(ISNUMBER(SEARCH(Sheet2!B2,Sheet1!A1)), Sheet2!A2), =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A1)), Sheet2!A3), etc So I am trying to do the same thing manually. I have played with VLoopup and Index but am only frustrating myself |
#4
|
|||
|
|||
Search Entire Sheet instead of 1?
Sorry let me try again.
Sheet1: Records Column1: Name | Column2: OriginCountry Japanese Doctors Association | English Breakfast Tea | American Cheese | Sheet2: Data Column1: Country | Column2: Denonym Japan | Japanese America | American British | England English | England Maltese | Malta So now I want to put a formula in Sheet 1/Column 2 that steps through Sheet2:Column2(Denonym) and if it finds a match updates the value for Sheet2:Column1 (Country) "Glenn" wrote: msnyc07 wrote: I have a function whereby if a string in Sheet 2/Column1 is contained in Sheet1/Column1, it returns Sheet2/Column1. I am doing this manually but would like it to step through the whole table in Sheet 2 (i.e. step through all for a match. Currently my formula looks like this; =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A3)), Sheet2!A3) To get it do do each manually of course I'd need to to =IF(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!A1)), Sheet2!A1, IF(ISNUMBER(SEARCH(Sheet2!B2,Sheet1!A1)), Sheet2!A2), =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A1)), Sheet2!A3), etc So I am trying to do the same thing manually. I have played with VLoopup and Index but am only frustrating myself This is a little hard to follow, but probably not that hard to accomplish if you could show some sample data and expected results. Also, where you want to put this formula would be helpful. . |
#5
|
|||
|
|||
Search Entire Sheet instead of 1?
I think you may have reversed some of your values for Sheet 2. Japan / Japanese
and America / American appear to be correct country / demonym (I think that is the correct term) pairings. However, British and English would both be demonym's for England, as would be Maltese for Malta. That said, I think Luke was on the right track, but with this clarification, I think it would look like this (array formula...commit with CTRL+SHIFT+ENTER): =INDEX(Sheet2!A:A,SMALL(IF(ISNUMBER(SEARCH(Sheet2! B$1:B$250,A2)),ROW(Sheet2!B$1:B$250)),1)) msnyc07 wrote: Sorry let me try again. Sheet1: Records Column1: Name | Column2: OriginCountry Japanese Doctors Association | English Breakfast Tea | American Cheese | Sheet2: Data Column1: Country | Column2: Denonym Japan | Japanese America | American British | England English | England Maltese | Malta So now I want to put a formula in Sheet 1/Column 2 that steps through Sheet2:Column2(Denonym) and if it finds a match updates the value for Sheet2:Column1 (Country) "Glenn" wrote: msnyc07 wrote: I have a function whereby if a string in Sheet 2/Column1 is contained in Sheet1/Column1, it returns Sheet2/Column1. I am doing this manually but would like it to step through the whole table in Sheet 2 (i.e. step through all for a match. Currently my formula looks like this; =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A3)), Sheet2!A3) To get it do do each manually of course I'd need to to =IF(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!A1)), Sheet2!A1, IF(ISNUMBER(SEARCH(Sheet2!B2,Sheet1!A1)), Sheet2!A2), =IF(ISNUMBER(SEARCH(Sheet2!B3,Sheet1!A1)), Sheet2!A3), etc So I am trying to do the same thing manually. I have played with VLoopup and Index but am only frustrating myself This is a little hard to follow, but probably not that hard to accomplish if you could show some sample data and expected results. Also, where you want to put this formula would be helpful. . |
Thread Tools | |
Display Modes | |
|
|