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).

Answer:
=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?

Thanks in advance for your help.

Regards,

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

1 Replies
1208 Views

Similar Articles

[PageSpeed] 58

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:
> 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).
>
> Answer:
> =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?
>
> Thanks in advance for your help.
>
> Regards,
>
> ZUO

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

Similar Artilces:

Auto change formula in cells when source is changed
Good day experts; If specific cells have the same formula for example: C1 = Product( A1;B1) C16 = Product(A16;B16) C17= Product(A17;B17) And so on ... What formula should i write in C16, C17, ... so that: when the formula in the source cell, C1, is changed the formulas in C16, C17, ... would automatically change accordingly. I tried "=C1" it does not work Thanks in advance Carlo carlo wrote: > Good day experts; > > If specific cells have the same formula for example: > C1 = Product( A1;B1) > C16 = Product(A16;B16) > C17= Product(A17;B17) > And so on ... ...

Custom formatting #7
How can I do the following custom formatting – I need trailing dots before the number ……..1,234.00 Thanks for any help Right Click > Format Cells > Select Custom Enter "........ "0.00 "John Knoke" <JohnKnoke@discussions.microsoft.com> wrote in message news:586761E6-32A9-4911-BF50-3790614145B2@microsoft.com... > > How can I do the following custom formatting - > I need trailing dots before the number > ....1,234.00 > > Thanks for any help > > John, Use a custom number format of *.#,###.00 Note the period between the * and the f...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

Maintenance Plan and Read Only DB
I'd like to use a Maintenace plan to run DBCC + Update Statistics on my DBs, but since i have one DB which is Read Only it stacs on that DB. What can I do? If the DB is read-only why update stats ? "Gal" <Gal@discussions.microsoft.com> a �crit dans le message de news: 345532F2-4689-44B0-8F39-1E637FD64179@microsoft.com... > I'd like to use a Maintenace plan to run DBCC + Update Statistics on my > DBs, > but since i have one DB which is Read Only it stacs on that DB. > What can I do? I forgot to mention that I like to use the option of &...

Why didn't .net 2003 fix known bug
Close to a year ago I asked about a problem I was having where a CDateTime control using the Time format would change to the Date format by itself. I was told this was a known bug. That was with .net 2002. I just upgraded to .net 2003, that bug being one of the reasons, and found it still exists. This is very frustrating. Is there anything that can be done when such a problem persists? I'm using the standard version so I do not have the ability to report bugs, unless I want to pay first of course. And having to pay to report a known problem just rubs me the wrong way. On...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use a separate cell for the dropdown choices, with their resulting value linked into the formatted cell through an IF function, using the character that you want to display.' So: =IF someone enters '1' in B8, THEN display contents of $C$4 (will it display font and attributes?) =IF someone enters '2' in B8, THEN display contents of $C$5 =IF someone enters '3' in B8, THEN display contents of $C$6 But I've been reading everywhere and CF is very new to me and I need ...

Formula #16
I need to have a formula that can add a row of cells with a variable number of colmns. Columns will be added and deleted everyday, so the formula needs to have a variable range. I hope this is clear. Thanks, Bernie ...

Excel formula copied down
Is there a way to copy down a column this formula =F3*A2 and have the A2 stay the same all the way down? The F3 would change on each row, F4, F5 etc. but the A2 would stay 'constant'. Thanks in advance for any help! =F3*A$2 -- Ian -- "Pat" <Pat@discussions.microsoft.com> wrote in message news:73505B29-538A-48C7-8F2F-F6126EF2126B@microsoft.com... > Is there a way to copy down a column this formula =F3*A2 and have the A2 > stay > the same all the way down? The F3 would change on each row, F4, F5 etc. > but > the A2 would stay 'constant'...

Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails Hi all. I'm new to this site and would appreciate some formatting help. I created a new Word doc and copied and pasted some graphics and some simple text boxes in to it. The graphics and text boxes were created in Powerpoint by someone else. Looks fine on my screen. Before sending it out via e-mail to an Outlook e-mail group I have, I first sent myself a test message to see what it would look like on the receiving end. It looks a mess! The text boxes are all over the place, as is the text that is not in a box. Also, everything slide over to ...

2003 format in 2007?
I'm used to Outlook 2003, but I just bought a new computer and installed Outlook 2007. I view a week at a time, and I prefer the calendar format in 2003 -- it shows the whole week in two columns and I didn't have to scroll. Is there a way I can use the same format in 2007? No, unfortunately that view had to be removed to enabled calendar overlay. Try a two week view: http://slipstick.me/2wk -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dai...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

polynomial formula for a line graph...
Hi. I haven't looked at this feature in a couple of years. Excel can give you the formula for a charted function... what I mean is, given a few data points, it can create a best fit curve, and display the function that it used to do that... Would someone remind me where, please? Thanks. Hi, Right click the data series and pick Add Trendline. From the dialog you can choose the type of trend line and whether to display the formula. Cheers Andy mark wrote: > Hi. > > I haven't looked at this feature in a couple of years. > > Excel can give you the formula for ...

simple? pasting simple formula, error on copy
I have some store buying spreadsheets I made up. Very very basic, one of the columns has a total at the bottom or end as you may call it for total $ amount bought. that final number is in c33. c1, c2, c3 etc is a amount from each vendor. I want to have a column telling me the percentage being spent on each, simple enough so I make a column with the following formula =c33/c5. I get a number, change it to percentage and BAM I have exactly what I want. I pasted that formula and pasted it into all the other rows of that certain column, but I keep getting the #div/0 error, cause the new formulas...

formatting #2
Is the strikethrough formatting option no longer available in Publisher? Or if you need to use a strikethrough do you have to draw a line through the text? The line does not always stay in the same spot when you group and then have to paste on another page. Waggie wrote: > Is the strikethrough formatting option no longer available in > Publisher? Or if you need to use a strikethrough do you have to draw > a line through the text? The line does not always stay in the same > spot when you group and then have to paste on another page. ========================== You have the an...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

Text formatted email
I am trying to email a long hyperlink path to some one that can only receive text formatted emails. When they receive the email it, the hyperlink gets converted in to 2 lines, top line is a hyperlink (path incomplete) the second line is "text" with the balance of the hyperlink path. I placed < > around the hyperlink and it still splits in to 2 lines. Is there any way to keep the full link path intact? Thanks! jvs Tiny URL to the rescue! :-) http://tinyurl.com/ -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Cr...

How do I make this formula?
I have an Excel worksheet to track my 401k performance. I have a row for Date, the Balance corresponding to each date, Contributions, a formula to calculate my Total Contributions, and a formula to calculate my Total Gain/Loss. My question is, how to I get my Gain/Loss formula to update corresponding to a new Date/Balance? Is there a formula function to make the formula ignore Column data in the previous Rows? Thanks! There will be, just add/subtract items from that row. Can you show us an example with data. -- HTH RP (remove nothere from the email address if mailing direct) &...

Reading empty query returns
When I open a form that has a bound query as the record source, no data appears (including the control objects of the form) if the query's select statement does not finds records that match the criteria I specify. This causes a problem in my VBA code as the variable I use to check the number of records returned by the query does not seem to be able to read empty query returns, even if I use isnull. So I guess I am asking how can I get Access to read an empty result set in VBA from a SQL select statement that is run??? Here is part of my code for this: DoCmd.OpenForm stDocName2, , ...

Import data not in correct format
Import data reads: Need to do statistical anaysis on hundreds of numbers and Cell value # of Cells don't want to enter the numbers by hand. 3 1 4 2 Would like: Cell Value 3 4 4 ...

how to increase text size in reading pane
dennist685 dennist685 <dennist685@nospam.gmail.com> wrote: > dennist685 Depoends on your Outlook version, which you decided wasn't important enough to mention. -- Brian Tillman 2007 tjanks "Brian Tillman" wrote: > dennist685 <dennist685@nospam.gmail.com> wrote: > > > dennist685 > > Depoends on your Outlook version, which you decided wasn't important enough > to mention. > -- > Brian Tillman > > dennist685 <dennist685@nospam.gmail.com> wrote: > 2007 I don't have OL 2007, but in OL 2003, the Readi...

Four Conditional Formats?
Dear all, I am attempting to set up a template for someone else to use on another machine, and wish conditional format a cell such that it has four potential formats. The first is based on an adjacent cell value, and if this is met, then it is colour 1. The remaining three, are all AND statements involving the first cell and another cell value being less than date A, less than date B or less than date C. Is this possible to do? I have limited VB knowledge, and am wary of using add-ins as the people I am sending this to to use have limited excel knowledge. Regards, Steve -- stevepain -...