Finding a string/using adjacent data question

It's a convuluted question that I hope has an easy answer. I'm rather
new to Excel formulas, so I've been trying to find stuff about this on
the internet but with not much luck. If anyone can help me at all I
would greatly appreciate it.

I have text strings in column A, A2-A335. I have numerical values that
correspond to each text string in columns B-O.

What I'm hoping to do is run a search for a given text string in column
A and then use the numerical values in D, H, and O for further
calculations.

I want to be able to type in a name in column Q (for example) and have
the formula return the values from the same row in columns D,H, and O
in descending format below the name. So, if I typed in, 'Bob,' in cell
Q2, for example, below my name it might return:

45 (from column D)
73 (from column H)
26 (from column O)

If you're still confused by what I'm requesting, I'll rephrase it as
best I can in sentence form:

First cell below the name:

"Search column A for the string given directly above this cell (Q2,
perhaps). If the specified string is found, return the number in that
row from the D column."

Second cell below the name:

"Search column A for the string given two cells above this cell (again,
Q2, perhaps). If the specified string is found, return the number in
that row from the H column."

Third cell below the name:

""Search column A for the string given three cells above this cell
(again, Q2, perhaps). If the specified string is found, return the
number in that row from the O column."



I'm not sure if this can be done but if anyone knows that it can be and
how, I would really appreciate it. If you need more information, say
the word and it will be here.

0
bobwothe (1)
1/9/2006 2:53:23 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
564 Views

Similar Articles

[PageSpeed] 7

Hi
One way,
in Q3 =VLOOKUP($Q$2,$A$2:$O$17,4,0)
in Q4 =VLOOKUP($Q$2,$A$2:$O$17,8,0)
in Q5 =VLOOKUP($Q$2,$A$2:$O$17,15,0)

-- 
Regards

Roger Govier


<bobwothe@gmail.com> wrote in message 
news:1136818403.782664.202880@g49g2000cwa.googlegroups.com...
> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
>
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
>
> What I'm hoping to do is run a search for a given text string in 
> column
> A and then use the numerical values in D, H, and O for further
> calculations.
>
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
>
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
>
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
>
> First cell below the name:
>
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
>
> Second cell below the name:
>
> "Search column A for the string given two cells above this cell 
> (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
>
> Third cell below the name:
>
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
>
>
>
> I'm not sure if this can be done but if anyone knows that it can be 
> and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
> 


0
roger5293 (1125)
1/9/2006 3:13:42 PM
in Q3: =VLOOKUP($Q$2,A2:D335,4,FALSE)
in Q4: =VLOOKUP($Q$2,A2:H335,8,FALSE)
in Q5: =VLOOKUP($Q$2,A2:O335,15,FALSE)


Regards,
Stefi

„bobwothe@gmail.com” ezt írta:

> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
> 
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
> 
> What I'm hoping to do is run a search for a given text string in column
> A and then use the numerical values in D, H, and O for further
> calculations.
> 
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
> 
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
> 
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
> 
> First cell below the name:
> 
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
> 
> Second cell below the name:
> 
> "Search column A for the string given two cells above this cell (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
> 
> Third cell below the name:
> 
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
> 
> 
> 
> I'm not sure if this can be done but if anyone knows that it can be and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
> 
> 
0
Stefi (275)
1/9/2006 3:16:02 PM
you want the VLOOKUP function.  It will look like this.

Q1: "Text Here"
Q2: =VLOOKUP(Q1,A2:O335,4,FALSE)
Q2: =VLOOKUP(Q1,A2:O335,8,FALSE)
Q3: =VLOOKUP(Q1,A2:O335,15,FALSE)

"bobwothe@gmail.com" wrote:

> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
> 
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
> 
> What I'm hoping to do is run a search for a given text string in column
> A and then use the numerical values in D, H, and O for further
> calculations.
> 
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
> 
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
> 
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
> 
> First cell below the name:
> 
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
> 
> Second cell below the name:
> 
> "Search column A for the string given two cells above this cell (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
> 
> Third cell below the name:
> 
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
> 
> 
> 
> I'm not sure if this can be done but if anyone knows that it can be and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
> 
> 
0
Sloth (218)
1/9/2006 3:21:04 PM
Hi

I'm sorry, I was testing on a smaller range and only went to row 17. You 
need to extend to row 335
The formulae should also be wrapped in IF() statements also, to prevent 
#N/A when there is no value entered in Q2

in Q3 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,4,0))
in Q4 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,8,0))
in Q5 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,21,0))


-- 
Regards

Roger Govier


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:uXutJ9SFGHA.3172@TK2MSFTNGP10.phx.gbl...
> Hi
> One way,
> in Q3 =VLOOKUP($Q$2,$A$2:$O$17,4,0)
> in Q4 =VLOOKUP($Q$2,$A$2:$O$17,8,0)
> in Q5 =VLOOKUP($Q$2,$A$2:$O$17,15,0)
>
> -- 
> Regards
>
> Roger Govier
>
>
> <bobwothe@gmail.com> wrote in message 
> news:1136818403.782664.202880@g49g2000cwa.googlegroups.com...
>> It's a convuluted question that I hope has an easy answer. I'm rather
>> new to Excel formulas, so I've been trying to find stuff about this 
>> on
>> the internet but with not much luck. If anyone can help me at all I
>> would greatly appreciate it.
>>
>> I have text strings in column A, A2-A335. I have numerical values 
>> that
>> correspond to each text string in columns B-O.
>>
>> What I'm hoping to do is run a search for a given text string in 
>> column
>> A and then use the numerical values in D, H, and O for further
>> calculations.
>>
>> I want to be able to type in a name in column Q (for example) and 
>> have
>> the formula return the values from the same row in columns D,H, and O
>> in descending format below the name. So, if I typed in, 'Bob,' in 
>> cell
>> Q2, for example, below my name it might return:
>>
>> 45 (from column D)
>> 73 (from column H)
>> 26 (from column O)
>>
>> If you're still confused by what I'm requesting, I'll rephrase it as
>> best I can in sentence form:
>>
>> First cell below the name:
>>
>> "Search column A for the string given directly above this cell (Q2,
>> perhaps). If the specified string is found, return the number in that
>> row from the D column."
>>
>> Second cell below the name:
>>
>> "Search column A for the string given two cells above this cell 
>> (again,
>> Q2, perhaps). If the specified string is found, return the number in
>> that row from the H column."
>>
>> Third cell below the name:
>>
>> ""Search column A for the string given three cells above this cell
>> (again, Q2, perhaps). If the specified string is found, return the
>> number in that row from the O column."
>>
>>
>>
>> I'm not sure if this can be done but if anyone knows that it can be 
>> and
>> how, I would really appreciate it. If you need more information, say
>> the word and it will be here.
>>
>
> 


0
roger5293 (1125)
1/9/2006 3:59:29 PM
Reply:

Similar Artilces:

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...