#### VLOOKUP formula #4

```I have a workbook with 2 sheets - sheet 1 and sheet 2.  I have a blank
column in sheet 1 (B), which I want to fill with info from column B in
sheet 2, but ONLY is the info in column A in sheet 2 matches with info
in column C in sheet 1.  I think I should be using VLOOKUP and had a
go with the following formula

=IF('A20'!A2=('Course Notes'!C5),VLOOKUP('A20'!A2,'A20'!A1:D4467,2))

direction?
```
 0
k.roberts (4)
11/11/2004 3:01:56 PM
excel.misc 78881 articles. 5 followers.

2 Replies
451 Views

Similar Articles

[PageSpeed] 10

```Hi
try
=IF(ISNA(MATCH(A20,'A20'!C2:C4467,0)),"no
match",INDEX('A20'!B2:B4467,MATCH(A20,'A20'!C2:C4467,0)))

"KLR" wrote:

> I have a workbook with 2 sheets - sheet 1 and sheet 2.  I have a blank
> column in sheet 1 (B), which I want to fill with info from column B in
> sheet 2, but ONLY is the info in column A in sheet 2 matches with info
> in column C in sheet 1.  I think I should be using VLOOKUP and had a
> go with the following formula
>
> =IF('A20'!A2=('Course Notes'!C5),VLOOKUP('A20'!A2,'A20'!A1:D4467,2))
>
> direction?
>
```
 0
frank.kabel (11126)
11/11/2004 3:14:04 PM
```So you have a value in Sheet1 column C to look up in sheet 2 column
and return the value from sheet 2 column B (is that right ?).

The standard formula for this would be (in column B or anywhere else i
the same row):-
=VLOOKUP(C1,Sheet2!\$A\$1:\$D\$4467,2,FALSE)

This, however gives #N/A if no match found, so we use something lik
this, which traps the "error" and puts 0 instead :-

=IF(ISERROR(VLOOKUP(C1,Sheet2!\$A\$1:\$D\$4467,2,FALSE)),0,VLOOKUP(C1,Sheet2!\$A\$1:\$D\$4467,2,FALSE)

--
Brian

-----------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5

```
 0
11/11/2004 3:30:48 PM

Similar Artilces:

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =\$B\$2*3 becomes =sheet1!\$B\$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's \$B\$2 (equivalent of sheet2!\$B\$2). I have tried entering =!\$B\$2*3 which works initially but recalculates using the \$B\$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Help #4
When you go into, Sales Batch Entry, select the batch, transactions, lookup by document number, the view I am getting now is green and white. I am seeing all the documents in all the batches. I don't want this view, I want to be able to view only what is in the batch selected. What controls this feature? rcr, what you can do is Go to Sales Transaction Entry, Enter your Batch ID, then if you see the lookup, it'll only have the transactions entered for that Batch alone. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "rcr" wrote: > When you go...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

validation list #4
hi i have created a validation list with more then 500 name, but when i try to find a particual name i have to go through all of them . is it possblie to write the first alphabitic letter in the list and the drow down list will point to that particual name e.g if the name in the list is Mark S. Mark can i write only Ma to find all Names start with Ma You can do this if you use a combobox from the control toolbox instead of DV. -- HTH Bob Phillips "osaka78" <osaka78@discussions.microsoft.com> wrote in message news:12E9A3FF-5773-4002-8BCD-C2498BDE9EB0@microsoft.com......

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

No return on basic search of activities, CRM 4.0
If I go into activities and view all of my activities I get the full list of phone calls and emails. However if I try to search for an activity that I've carried out with a contact i.e. John Smith, The Company. I get no results returned if I search on either term, even though an activity on this account is in the list. If I go to Advanced Find, I can search for John Smith and/or The Company and the contact is then returned in the search. Why is the basic search not working? (I'm using the web client) Thanks in advance! Hi, Have you configured your required columns to be ...

sum if formula
=SUM(IF('Day 1'!\$C\$4:\$C\$37=A22,'Day 1'!\$D\$4:\$M\$37))+SUM(IF('Day 2'!\$C\$4:\$C\$37=A22,'Day 2'!\$D\$4:\$M\$37))+SUM(IF('Day 3'!\$C\$4:\$C\$37=A22,'Day 3'!\$D\$4:\$M\$37))+SUM(IF('Day 4'!\$C\$4:\$C\$37=A22,'Day 4'!\$D\$4:\$M\$37))+SUM(IF('Day 5'!\$C\$4:\$C\$37=A22,'Day 5'!\$D\$4:\$M\$37))+SUM(IF('Day 6'!\$C\$4:\$C\$37=A22,'Day 6'!\$D\$4:\$M\$37))+SUM(IF('Day 7'!\$C\$4:\$C\$37=A22,'Day 7'!\$D\$4:\$M\$37))+SUM(IF('Day 8'!\$C\$4:\$C\$37=A22,'Day 8'!\$D\$4:\$M\$37))+SUM(IF('Day 9'!\$C\$4:\$C\$37=A22,'Day ...

Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that made it disappear. If I go to the “View”menu, Formula Bar has a check next to it, as if it should be showing, but I can’t see it. I tried checking and un-checking, but no where to be seen. I have two other tool bars and my status bar, that are working fine. Could anyone give me a hand with this? Thanks, I am currently using Office v. X. You may have switched "Full screen" on. To uncheck, see the View menu -- Kind Regards, Niek Otten Microsoft MVP - Excel "JorgeH" <JorgeH@d...

I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

I make Outlook 2000 use a Outlook.pst file on a separate hard disk instead of the usual C:\Documents & Settings\ folder. Everything is fine. I also TICK "Leave copy of message on server" so I always have a backup copy. Now when i format my PC and reinstall Windows & Outlook, I point to my D:\Outlook.pst and it starts using it. Then all of a sudden it starts downloading ALL my emails again. If I format my PC once again and reinstall Windows & Outlook, then Outlook starts downloading ALL my emails TWICE (there's 2 copies of each email downloaded, plus the copy I...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for so...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

VLOOKUP with partial match
How can I get a match with VLOOKUP("TBS";a1:c15;2) if in the Table_array the data is "TBS XYZ" ? ...

i want to mail merge 1 address per postcard 4 to a page,
I have postcards preprinted on one side. I have made a publication using mail merge to add addresses. But I have 4 postcards on a page when I do the mail merge I am getting 4 postcards with the same address. How do I get one address per card but do 4 to a page? What version Publisher? In some earlier versions the print preview showed all the addresses the same, it was/is a Publisher bug. In Publisher 2007, once you have your postcard created --- File, print merge, on this screen, select multiple copies per sheet and landscape. There is a print preview on this screen. -- Mary Sauer ...

Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) wit the column range (E6:E35). Is it possible to include a multiple sheet count and column range i the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to rea multiple sheets.. Please help on this -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=...

Outlook Profiles #4
Does anyone know how to auto-configure Outlook profiles (exchange server, alias, .pst, etc) at logon based on username??? Create a prf-file with the variable %username%. Instructions on the prf-file can be found in the Office Resource Kit -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Save Embedded Pictures in Their Original Format -Create an Office XP CD slipstreamed with Service Pack 3 ----- "MessageQuestor" <crayola5150@excite.com> wrote in message news:05790...

Formula not deleted when pressing delete???
Hi, Is it possible to protect Formula's in a cell from being deleted whe user pressing delete? Only the cells who containt formula's may not be cleared but th others may! Thanks in @dvance for trying to help me. GreetZ from Stif -- Message posted from http://www.ExcelForum.com Stift, Select the cells that are allowed to be changed. Format - Cells - Protection - Unlock. Now protect the sheet: Tools - Protection - Protect Sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Stift >" <<Stift.18ct...

Subtotal #4
Hi, I have a list of records as follows Apple orange grape apple apple grape watermelon I want to count the number of unique item above...ie, the end result should show 4 (apple,orange,grape,watermelon). Is there a formula to do this? Thanks! Val Hi Val, I suggest you look here: http://www.cpearson.com/excel/duplicat.htm#CountingUnique for a solution by Chip Pearson tim dolphinv4 wrote: > Hi, > > I have a list of records as follows > > Apple > orange > grape > apple > apple > grape > watermelon > > I want to count the number of unique item ...

Drag Formula cell to decrement the cells #2
Ken, I do thank you for your prompt assistance. The solution you gave me works fine but with limited No. of cell (sorting problem) is there a way to do it in an unlimited No. of cells (e.g. 50 or 6 cells - BI till DP to be the mirror of A till BH ) Thanks Agai -- Asher Zu ----------------------------------------------------------------------- Asher Zur's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1610 View this thread: http://www.excelforum.com/showthread.php?threadid=27554 Sure, just follow instructions as before and in BI1 put =A1 and copy across to DP...

Protect: Formulas?
I'm protecting a sheet via VBA. There seem tb quite a few parms to control what gets allowed/disallowed, but I can't find anything that applies to formulas. I want the user to be able to put formulas behind some cells. Right now, with the sheet protected, the user can type data into cells, but the Sigma icon that allows formulas tb put behind them is disabled. Unprotecting the sheet enables the icon... but I want various things to remain protected. Is there a way to allow formulas, but still have Protected=True? -- PeteCresswell Pete, You have to have unlocked the cells the use...

Need Help Nesting a Formula inside MAX formula
I have Stock Market quotes that gets stored in Excel intra-day When every 30 minutes passes, the data is automatically updated and archived in the next row up and the new update begins. EXAMPLE: C2 9:30 D2 25 C3 10:00 D3 25.5 C4 10:30 D4 25.10 C5 11:00 D5 26 After 30 mins the data looks like this: C2 10:00 D2 25.5 C3 10:30 D3 25.10 C4 11:00 D4 26 C5 11:30 D5 26.25 This process keeps going on. I can archive as many periods I want, in any timeframe. I currently store 100 rows of 30 minute periods. Everytime a new peiod begins al...

Strange Problem with Formulas
Dear users, I was confronted with a very strange problem. Can anybody help? Basically, I have 3 worksheets: No. 1 contains all data No. 2 contains all filtered data (macro special filter from No. 1) No. 3 is the output, graphically optimized sheet, which has fixed formulas to No. 2 (e.g. =Event1!A1) Imagine, I had only these 3 sheets. Actually, I have many of them, but there are all paired as No. 1 and 2. No. 3 is the main sheets, which links too all No. 2's. To update the file, I have created a macro, which compares one excel file with the actual one. All worksheets named similarily ...

inventory aging report #4
Hello: What is a good report in GP 9.0 to use if you want an inventory aging report? The stock status and historical stock status do not offer what I'm looking for. I want to see transactions in detail and their effect on inventory valuation. Thanks! childofthe1980s oh, and the report needs to also show the valuation affected by inevntory adjustments. So, the Purchase Receipts report is not good enough either. childofthe1980s "childofthe1980s" wrote: > Hello: > > What is a good report in GP 9.0 to use if you want an inventory aging > report? The stock...

CRM 4 Client and offline sync error
I recently upgraded our CRM to version 4. I have the offline client installed on my machine. When I try to go offline, I get an error during Propagating Customizations. It says: An entity with the specified name already exists. Please specify a unique name. I've tried disabling all of my local data groups in an effort to try and narrow down the issue, but even with them all decactivated I still get the error message. I also tried unchecking all of the sync boxes under on the options screen, doesn't seem to effect it. Anyone have any ideas on this one? Hi I had the same e...