Search Entire Sheet instead of 1?

  • Thread starter Thread starter msnyc07
  • Start date Start date
M

msnyc07

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 :)
 
msnyc07 said:
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 :)
Click to expand...


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.
 
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,Sheet1!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.
 
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)
 
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))
 
Back
Top