#### Fixing a formula to read a general format?

```A few months ago I had asked a question and got this answer:

Question:
I have a a set of values in A1 through A100.
I need to look up each value and find a match in
another set of values located in C1 through C200. If a match is found then I
need the formula located
in column B to return the value in the same row but
the next column over (D).

=IF(ISNUMBER(VLOOKUP(A1,\$C\$1:\$D\$200,2,0)),
VLOOKUP(A1,\$C\$1:\$D\$200,2,0), "")

Copy down through A100.

The formula worked fine, however the reference value and the look up value
are not any more numbers but a combination of letters and numbers (i.e.
T-410TFG2-A) with a general format and the formula is not reading them. How
can I fix the formula?

Regards,

ZUO
```
 0
Utf
5/16/2010 11:05:01 PM
excel.worksheet.functions 4936 articles. 0 followers.

1 Replies
532 Views

Similar Articles

[PageSpeed] 52

```Just use the Vlookup part:

=VLOOKUP(A1,\$C\$1:\$D\$200,2)

or maybe this with error checking:

=IF(ISNA(VLOOKUP(A1,\$C\$1:\$D\$200,2)),"",VLOOKUP(A1,\$C\$1:\$D\$200,2))

Regards,
Per

On 17 Maj, 01:05, Zuo <Z...@discussions.microsoft.com> wrote:
>
> Question:
> I have a a set of values in A1 through A100.
> I need to look up each value and find a match in
> another set of values located in C1 through C200. If a match is found then I
> need the formula located
> in column B to return the value in the same row but
> the next column over (D).
>
> =IF(ISNUMBER(VLOOKUP(A1,\$C\$1:\$D\$200,2,0)),
> VLOOKUP(A1,\$C\$1:\$D\$200,2,0), "")
>
> Copy down through A100.
>
> The formula worked fine, however the reference value and the look up value
> are not any more numbers but a combination of letters and numbers (i.e.
> T-410TFG2-A) with a general format and the formula is not reading them. How
> can I fix the formula?
>
>
> Regards,
>
> ZUO

```
 0
Per
5/17/2010 12:02:10 AM

Similar Artilces:

Best way to format a flat file that Excel will load properly?
I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a ..txt file, although I hope to save it in a delimited flat file format with an ..xls extension so it will automatically be opened by excel without having to go through the whole 'import' sequence. There are string fields in the file, some of which contain commas- so I can't make it a comma delimited file. What is the best delimiter to use so that Excel will automatically recognize and parse the flat file contents across columns? Currently in 2003, but strongly prefer solutions ...

Read text as a cell for a formula
I have two formulas in two cells, one spitting out a value for a colum (A) and the other a value for a row (2). In the a third cell I'm addin the letter and number together to create a cell address (A2). I want t use this information in a fourth cell for a formula (=A2*\$B\$10). But, how do I get Excel to recognize the text "A2" as a value for cell and then add A2 (or whatever the third cell may be) in to formula? Any help? Thanks, Nat -- Message posted from http://www.ExcelForum.com Hi Nate try =INDIRECT(A4)*\$B\$10 where A4 stores your cell reference as text, e.g. 'A2&...

Reading and writing into a file
Hi, We use Great Plains accounting software to generate checks etc. We want to explore the feature if Great plains can read a fixed format file and write into a file. We are trying to look into this possibility because we want this application to share data and interface with another application written in powerbuilder. Please help. Thanks and Regards, Vinay Using Integration Manager or eConnect, Great Plains can read from a fixed file. Using eConnect, SmartList, or some other tool Great Plains can export to a file. eConnect may be the best bet if you already have developers. "V...

Circular reference, Maximum number of formulas
I have come across a spreadsheet in Excel 2000 which exceeds the 65536 number of formulas that Excel's dependency table can keep track of. The consequence is that 'Calculate' is permanently diplayed in the status bar, AND (much more important), it cannot keep track of circular references. By opening the file on my machine with Excel 2003 SP3, I was able to locate the circular references and correct them. What is the max number of formulas that Excel 2003 can track in its dependency database? What about 2007 does that increase the number of formulas that can be tracked? Th...

How do I fix this?
I used the Startup tool to setup my application but I unchecked the Menu Bar and the Shortcut Menu Bar checkboxes. I need to make some changes but I can't get the Tool menu back. Help Please. Thanks Hold down the Shift key when you open your database. Rob "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:C3A9F20A-231C-42CA-80C4-59B836CFE1AB@microsoft.com... >I used the Startup tool to setup my application but I unchecked the Menu >Bar > and the Shortcut Menu Bar checkboxes. I need to make some changes but I > can't > get the Tool menu ba...

Need to convert 2002 back t0 while laptop is fixed
My laptop with Money 2002 died and had to be sent in for service which will probably be about a month. The good news is that I backup up all my data files the day before and burned them to CD. My office machine is running Windows 98 SE and Money 2001 but it will not read the 2002 files. I can't install XP because the machine will not support it. I used a friend's machine and can open my backup file but I don't want to leave my financial data there. Is there some way I can transfer the data to Excel or save it in a backwardly compatible format? I tried to synchronis...

very soon i will be starting a tafe course involving advanced spreadsheets, my question is: Is there a publication available ,where i can access all posible formulas for this work ? Hi Cobber_Oz! Functions lists available from my email below. Also see: Peter Noneley: http://homepage.ntlworld.com/noneley/ -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "COBBER_OZ" <anonymous@discussions.microsoft.com> wrote in message news:C6E477CF-CE34-40B...

Formatting toolbar disappears randomly.
My formatting tool bar disappears randomly when I create a new email. I put it back and for a time it stays there then other times it just does not appear when I try to create a new mail. I have installed all the Office service packs as well as all the windows update. Can anybody offer a solution? Many thanks. Mark Baker Try renaming your outcmd.dat file to outcmd.old while Outlook is closed. Outlook will rebuild the default toolbars the next time you open it. If your toolbar locations "stick" after doing this, then you know it was the outcmd.dat file causing the problem. ...

delimit using a formula
I am wanting to delimit a cell with both text and numbers in to separate out the numbers which have common characters that delimit them from the rest of the text. I can do this using text to columns but I want to know whether I can do this by using a formula instead.##e.g. A 1 text (2/3) more text 2 text (3/4) more text I want to get the results: A B 1 2 3 2 3 4 using the first delimiter "(", then "/" then ")" is there a way to write this...

Formula ! need help.
Hi, How to express the following formula in Excel format; Formula 1 , ((A3+B3)/2)(B30/365) Formula 2 , ((A5+B5)/2)/(B31(365)) I did try but not success. Thanks I can see a couple of mistakes =((A3+B3)/2)(B30/365) Needs an operator (+-*/) between ..2) an (B30... =((A5+B5)/2)/(B31(365)) the last term does not work (B31(365)) shoul be (B31/365) or simila -- Alex Delamai ----------------------------------------------------------------------- Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127 View this thread: http://www.excelforum.com/show...

Fixing column width
I have a spreadsheet in which I have set the widths of certain columns using Autofit. The problem is that I often have to add to the spreadsheet by importing data from a text file. Every time I do that, the column width changes to the width of the new data (which is usually narrower than what I wanted). Is there a way to prevent that from happening? Thanks, Cliff Lewis Assuming you're using the Data, Import Data command, when you get to the final "Import Data" dialog there is a "Properties" button (in Excel 2003 at least). One of those properties is Adjust column w...

Chart to monitor Blood Pressure readings and date
Does any one have any ideas on how to set up a chart to show daily readings of Blood Pressure? I want to chart readings over one month intervals. Need to figure out how to insert variables (123/82) and date of reading and get a meaningful representation. Do you intend to capture BP once a day or more often? If, for example, you want to take a reading every morning and one every night then you would set up your spreadsheet with 5 cloumns (date, am diastolic, am systolic, pm diastolic and pm systolic (can't remember which of the two is the higher, but you get the drift). Then, a...

ANONYMOUS LOGON and Everyone Read Permission on User Mailbox
Hello, I've been trying to get my first Exchange Srever 2003 up and running. It is running on an SBS 2003 Premium box. One thing I notice that is very strange is that if you go to Exchange Advanced:Mailbox Rights for any user in the ADUC both ANONYMOUS LOGON and Everyone has inherited Read permissions. It is not clear if that is a problem ... but my guess is that it allows others to read users mail? I've been searching online as well as the knowledge base, but there is nothing that clearly discusses this. It does seem others have noted this problem, but no one really describes...

Format- problems
I have a query with the following : Format(tblAvvikelser.regDate,"yyyy/mm"). If I put in 2010-05-27 (swedish date format), it should return 2010-05. On some clients it works fine but in others I receive an error, telling me that the format can not be solved. Why??? Copied form a post I saw about 1 month ago: Access will struggle to interpret a date. The text string "3/10/2010" will be interpreted as March 10 (even if the user intended it to be 3 October), but it will give a JDate value. "4/30/2010" will similarly be April 30 (and it won't try i...

Help with Formulas #2
I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for co...

The great disappearing hot fix
Has anyone experienced anything like this? Yesterday, one of our users complained to me about some quirk in GP eConnect processing that I had thought we fixed in December of 2007 via a Hot Fix to eConnect. Well, when I checked our server it was as if the hot fix was never installed (the eConnect release version was the basic 9.0.3.0, instead of 9.0.3.4). The Hot Fix was also missing from Add/Remove Programs. I went to reinstall the hot fix, and when the install program started it gave me the option to either Repair or Remove the hot fix. So, something was still present tha...

Error importing dates from fixed width txt files
I'm trying to revise an existing Import Spec to include new fields added to a fixed width text file. If I import without making any revisions the dates import correctly. They are on the file as CCYYMMDD and the data field in the Spec is Date/Time and I've deselected 4 digit year. My new date fields were added to the text file as YYMMDD and I select Date/Time data field for the new fields. However, with the revisions made adding the new fields none of the date fields import now with the revised spec. Another user created the spec and we're both using Access 2003 with th...

Formula with no result
I have clients that I am intruducing starting with a date. After exactly 10 days, I have to pay them some money. I would like to be notified by changing colour of the their rows, and not changing back until I complete the cell of paiment, that is, of course, in the same row. After I reveived help from this forum, I've tried to do the next things: I have: A1 Z1 - TO PAY 10/07/2008 0 15/07/2008 30 05/07/2008 50 Z1 - TO PAY cell ads the total amount the money that I have to pay in other cells. This is wh...

Array formulae do not recalculate
I have a large number of array formulae in my sheet. I update the supporting data and the formulae do not recalculate. The only way i can get a recalc is to F2 and then ctrl+shift+enter. How can I get all of the formulae to recalc. Manual recalc does not work. Does Automatic Calculation mode work? If excel is confused about whether it should recalculate a formula, this sometimes wakes excel up and stops the confusion. Select all the cells on that sheet (ctrl-a a few times should do it) Edit|replace what: = (equal sign) with: = replace all Excel will see that ...

Cell Box - one of the 4 cell lines is open
What was the set of search "words" to find the answer... please? I have spent an hour. ( I don't want to tell you that I spent more time - mentioned). Oh yea! The lines are call borders and are part of cell formatting. Format > Cells... > Border > Outline > OK -- Gary''s Student - gsnu200826 "MUTTMIND" wrote: > What was the set of search "words" to find the answer... please? I have > spent an hour. ( I don't want to tell you that I spent more time - > mentioned). Oh yea! No worky. Excel 2007 - home tab - cells (ins...

How to delete "read only" opportunity and Offer
I have de administrator role for CRM 1.2. One of the users added by mistake 10 or more Opportunities and Offers which I can not delete. The program sends a "Read Only" message qhen I try to delete them. How can I erase this items???? Many thanks to whom may help. ...

Formula to show the content of a formula
Hi, Is there a way to show the actual content of the formula. I know you can use the FIND function to search the content, but is there a way of displaying it as well? Kind of like hitting ctrl+` but ony for certains cells. Say I want to check if "VLOOKUP" was used in column A. I would like to put some sort of formula in column B, and if one it's used then to show something like "check". Just so I know to go and check. All feedback are appreciated. Regards, David You can do it with a User Defined Function: Function CellFormula(CellData As Range) As String CellFo...

Date formula
I am preparing a spread sheet for validity of cards and the trainng details If the card issue date is 14/12/2009 and is entered into a cell For eg D1 Can I have formula in a cell to calculate and come up as 14/12/2010 in E1 as the validity of card is one year Thanks as always Afd Put this in E1: =3DDATE(YEAR(D1)+1,MONTH(D1),DAY(D1)) Hope this helps. Pete On Dec 14, 12:17=A0pm, "afdmello" <afdme...@hotmail.com> wrote: > I am preparing a spread sheet for validity of cards and the trainng detai= ls > If the card issue date is 14/12/2009 and is...

Pre-Format email with addresses for supversior (Actions Buttons)
Is there a way a user can create an email in Outlook XP (using exchange server) that can contain pre addressing. Msg be saved then included in another msg to a supervisor where by when open the supervisor can reveiw orginal msg and then using action buttons, send the email out under their email address. Create the message and save it. Drag it into your Drafts folder. Then create the message to the supervisor and drag the first message into it. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers h...

Fix
What does the word FIX mean on Excel 2000? It is in the lower right hand corner 3 boxes away from NUM, 4 boxes from CAPS. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ It means you have turned on fixed decimals places under tools>options>edit, so if you have 2 fixed decimal places and enter 20 in a cell it will change into 0.2 Just uncheck it if you don't want it -- Regards, Peo Sjoblom "zacharias" <zacharias.vdc4c@excelforum-nospam.com&...