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 :)
0
Utf
3/17/2010 3:59:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
687 Views

Similar Articles

[PageSpeed] 58

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.
0
Glenn
3/17/2010 4:31:11 PM
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.
-- 
Best Regards,

Luke M
"msnyc07" <msnyc07@discussions.microsoft.com> wrote in message 
news:33DFF402-8E42-4A84-8895-5D7DFA518330@microsoft.com...
>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 :) 


0
Luke
3/17/2010 4:52:11 PM
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.
> .
> 
0
Utf
3/17/2010 8:05:02 PM
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.
>> .
>>
0
Glenn
3/18/2010 1:51:40 PM
Reply:

Similar Artilces:

R squared not the same in graph and spread sheet.
I have an X-Y plot displayed with a trend line equation and R squared. I also calculate R squared (RSQ) and the numbers are not the same; 0.8918 and 0.928. Can someone explain this (Excel 2010 and Windows 7)? -- Jim Silverton Extraneous "not" in Reply To. "James Silverton" <jim.silverton@verizon.net> wrote: > I have an X-Y plot displayed with a trend line equation > and R squared. I also calculate R squared (RSQ) and the > numbers are not the same; 0.8918 and 0.928. Can someone > explain this (Excel 2010 and Windows 7)? You would need to post [1] m...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Z Report shows zeros for an entire shift
How do I correct the problem described above? All receipts are printed correctly, and all transactions seem to be successful. However, the Z report that is run at the end of the shift prints all zeros? Any ideas??? I am having the same problem. All the transactions are successfull and I can see the sale amounts and etc at crstal reports but all the z, zz and x reports print "$0.00" amount. Does anyone know how to solve this problem? "Maakus" wrote: > How do I correct the problem described above? All receipts are printed > correctly, and all transactions seem t...

Incorporating detail of one sheet into another. #2
There are 2 excel worksheet. I would like to incorporate detail of sheet 2 into sheet1. e.g Sheet2 Contain the following details A B C D E F G Agent1 Agent2 Agent3 Agent4 Agent5 1 Apple Cycle 2 Mango Car 3 Banana Bus 4 Coconut Motor and so on in coloumn c , d , e,f and g. Now what I would like to do is If in sheet 1 If I select Agent 1 all the detail of sheet2 which has column as agent1 should appear exactly as it is in sheet2. And the same thing for Column B, c ...

How can I have more than 64000 rows in one sheet?
I want to import an access database in one excel sheet, it requires 200.000 rows. Any suggestions? "blafblaf" <blafblaf@discussions.microsoft.com> wrote in message news:C3D0F75D-7F4E-4F76-95F3-72D7F3AE8D7A@microsoft.com... >I want to import an access database in one excel sheet, it requires 200.000 > rows. Any suggestions? Hi BlafBlaf, The current worksheet row limit of 65536 is not expected to be increased in the immediately foreseeable future. To import your database to Excel, it would therefore be necessary to pass 64k tranches to each of four worksheets. This...

SP 1 & 2 Updates
I noticed that when I installed the SP 1 & 2 Updates, I could no longer get .zip, .doc, .htm, .exe attachments and anything like that (.jpg and .gif are no problem). Is there a way to enable attachments of other types to come through? I would like to install the Updates, but need to be able to receive attachments. Thanks for your help. ...

Send 1 address to 2 mailboxes
I want to create an address and have any mail sent to that address delivered to two separate users' mailboxes. How can I do that. I've tried simply adding the address in the two users' "Email Addresses" tab in the User's properties of ADU&C. But it wouldn't allow me to add the same address to the second user ("This email address already exists in this organization"). I've also tried creating a new user for the address and forwarding the email to the 2 other users (Exchange General tab - Delivery Options button - Forward To field). But ...

Formulas in Excel using [R-1] not A10 references
I converted a Word document into Excel 2003 and when creating formulas,they are formatted as the cell the formula is in plus or minus a number of rows or columns. An example: =SUM (R[-4]C:R[-1]C). I would like to see these formulas in the regular format referencing Row and Column number i.e. =Sum A1:V52 Tools >Options, the General tab, uncheck R1C1 reference style "Drregion" wrote: > I converted a Word document into Excel 2003 and when creating formulas,they > are formatted as the cell the formula is in plus or minus a number of rows or > columns. An example...

linking sheets
Hi All, How do i link my drop down list in a13 sheet 1 to column f in sheet 2. Hi Jinx if using Data Validation to create your drop down list the easiest way is to range name column F in sheet 2 (go to sheet 2, click on F, click in name box (left hand side of formula bar) type name (no spaces), press enter) then click in Sheet1!A13, choose Data / Validation, choose List - click IN the big white box, press you F3 key to bring up list of names in workbook, choose the name, click OK, click OK again and it should work fine. Hope this helps Cheers JulieD "Jinx" <anonymous@discus...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

Help Please - A bit of a challenge
Hello everyone~ I had had some trouble on a project I have been working on for some time. I have tried numerous approaches, each with undesired results. (VAB Code) The project I am working on has become quite complex, so rather than bother you with my spaghetti code, I have made a simple example of what I am trying to do. (Attached to this Message) The sheet tracks People and how many fruits they had each day. Day1 is where the information is Inputted, Day2 will double the DAY1 numbers, and Day three will triple the DAY2 Numbers. The challenge I am faced with: On DAY1 ...

Will DPM 2010 include Disk to Disk to USB Disk instead of tape?
In 2007 if I want to backup to usb for offsite I have to use a third party program like Firestreamer to make the usb disk look like a Tape. Will 2010 allow us to use usb drives or ESata drive for offsite storage? This would be way cool since Tape can be very expensive for large data sets... USB drives are cheap and again if goes bad get another one. Dan Thanks Dan for your feedback. We have been getting this request from our customers but so far this is not in the list of DPM 2010 features. I'll pass on your feedback to the product group. -- Gaurav Gupta | Program Manag...

copying rows from next sheet over
I'm trying to copy a range of rows from one sheet over from the activ sheet, and paste them onto another sheet. This is what I tried: ActiveSheet.Next.Range("a2:b100").select selection.copy However, I'm getting the error "selection method of the range clas failed" What am I doing wrong, and is there an alternative way to do this? Any help would be appreciated -- ayl32 ----------------------------------------------------------------------- ayl322's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984 View this thread: http://www.excel...

CRM 1.2 pro or standard
does anyone know if the action pack release of 1.2 will be standard or professional? Also, is the msdn universal version pro or standard? Shawn, My understanding of it is that the Action Pack is a Standard license while the MSDN is a Universal. However, the MSDN license is not valid to be used in a production mode, only for testing, developement & demoes. Matt "Shawn" <anonymous@discussions.microsoft.com> wrote in message news:024e01c3caa1$fa3e5400$a501280a@phx.gbl... does anyone know if the action pack release of 1.2 will be standard or professional? Also, is the msd...

Sorting Sheets?
In Excel 2002 is there a way of sorting sheets other than by dragging? See www.cpearson.com/excel/sortws.htm for sample VBA code to sort worksheets. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "rifleman" <me9@privacy.net> wrote in message news:bpikvi$1odhnd$1@ID-108938.news.uni-berlin.de... > In Excel 2002 is there a way of sorting sheets other than by dragging? > > Using VBA http://www.cpearson.com/excel/sortws.htm -- Regards, Peo Sjoblom "rifleman" <me9@privacy.net> wrote in mess...

TLS to 1 customer
We have 2 2000 servers and 1 5.5 server. On the 2000 servers I was able to add in a connector, specify the address space for the specific domain I want to send to and specify the TLS encryption. I am not familiar with 5.5 enough to find how to do this. What is the best way? On Fri, 30 Jun 2006 06:08:02 -0700, Ryan <Ryan@discussions.microsoft.com> wrote: >We have 2 2000 servers and 1 5.5 server. On the 2000 servers I was able to >add in a connector, specify the address space for the specific domain I want >to send to and specify the TLS encryption. > >I am not fa...

How do I print an entire workbook w/diff orientatns to Adobe PDF?
I'm trying to print to PDF an entire workbook that has different orientations (landscape/portrait) on each worksheet. It keeps asking to save each time the orientation changes so I end up having to combine the documents in the end. Is there any way to 'print' / convert the entire workbook in one go? Thank you. ...

MOD Function: MOD(7.0,1) = 1 Why? and how to work around it.
I'm using Excel 2003 spreadsheet, but I've tried this in Excel 2010 & OpenOffice.org. A similiar error occurs. You can see that I've also used the Microsoft suggested workaround but to no avail. Does anyone have an answer or possible suggestion. A B C Base Increment 5.0 0.10 Qty MOD(Qty,1) number-(INT(number/divisor)*divisor) Equiv. = Qty-(INT(Qty/1)*1) $A$2 5.0 0.0 0.0 SUM($A5,$B$2) 5.1 0.1 0.1 SUM($A13,$B$2) 5.9 0.9 0.9 SUM($A14,$B$2) 6.0 0.0 0.0 SUM($A15,$B$2) 6.1 0.1 0.1 SUM($A23,$B$2) 6.9 0.9 0.9...

Finding a sheet
Since you were so quick and helpful about my index question......... If I happen to know the name of the sheet is there a way to type the first few letters of the name and then find the sheet that way? Thanks Linda Find sheet in a file? Put your cursor down in the tab area -- all way to left and right click Select sheet "Linda" <RomulanQueen@StarfleetHeadquarters.xp> wrote in message news:%23C3cN1BjDHA.624@TK2MSFTNGP11.phx.gbl... : Since you were so quick and helpful about my index question......... If I : happen to know the name of the sheet is there a way to type t...

Outlook 2007 Search Problem
I work at the Help Desk for a University. A client has called complaining that recently Outlook 2007's search bar started returning "bad results." For instance, if she searches for Smith, she'll get e- mails that were sent to Smith, or have Smith in the body, but not e- mails that are FROM Smith. She used to get better results. I pointed her to the advanced search feature (available in a sub-menu, or available right there when Instant Search is installed), and showed her how to search specific fields. She's still not satisfied, though. It used to work, she claims, and sh...

How to search a cell for specific information and to pull out that figure found !
Okay , this maybe a tough one....but I hope its solvable , basically I am uploading products to one of the more popular websites , they have various columns , one of which is comment , in this I can type in up to 1000 characters but cannot contain "<" or ">" The comment section I would like to be able to add at the end something like (min=6.75) , this would be my min selling price. There system allows you to download a re-pricing report, it shows only items where you are not the lowest price, but it's a mix of various products all have various min sell...

How do I get a list of data on one sheet into the form on the fir.
I have a timecard form on the first tab of a workbook & the various employee crew combinations on each of the subsequent tabs (each crew on a seperate tab). How do I format the for to get the employee name info from the individual crew tab required? Let me clarify my question - I am trying to use the first tab (the form) as a constant form & just change the employees names in that specific field based on the crew I want to generate the timesheet for. The crew lists are on the sugsequent tabs. I would like to be able to select the crew & print the form with only those emp...

Installation CRM 1.2 on SBS2003 SP1 fails, please help
At the end of my installation I get an error windows with; Setup was unable to install Microsoft CRM Server Setup was unable to provision your organisation Setup was unable to create user settings. Access is denied. (80070005) Active Directory is in native mode I am installing as the administrator (domain admin) security account for services in Local System account Local System account and computer are added to the Pre Windows 2000 comp group Thanks in advance for any support Marc Did you enter your organization name precisely the same way it appears on your CRM 1.2 licenses? -- Matt ...

Printing Row 1 at top of each page
This seems obvious, but I cannot get it to work. I am trying to print out a doucument that has multiple pages, and I would like to have the header information that is in Row 1 at the top of each printed page. I have gone into Page Setup, and the tab called "SHEET", but it will not let me enter any info in the "Print Titles" section. Is there something that I need to do first in order to make these changes enabled? Thanks Lance Gray "Lance Gray" <Lance Gray@discussions.microsoft.com> wrote in message news:D1734460-9541-4AA4-A7DB-A52F67C863FC@microsoft.c...

need to shift dates forward 1 year
greetings! I have an annual schedule that, after some editing, needs to have all dates in a column moved forward 1 year. is there a way to accomplish this without actually mannually re-entering the dates? TIA Joan Depending on your Date Format, you could Select the Column, and then do an Edit|Replace. Ex: For: 1/01/2004 Replace: 2004 With: 2005 Make sure you select the column first, if you plan to use Replace All. tj "joan" wrote: > greetings! > > I have an annual schedule that, after some editing, needs to have all dates > in a column moved forward 1 year. is ...