#### Return vlookup result but only for the first hit

```Hi all, I hope someone can help with this one - I think I'm overly
complicating my problem here - hoping there's a simple answer - it's a
tricky one to describe though....

Here's the overview.
3 rooms can be booked for courses where the customer will use minutes
in subsequent visits.
When making a booking, a "customer" tab is used to identify the price
of the couse and gives the customer a unique number e.g. 5023Tony
Summers (concatenated for a lookup)
In the diary planner tab, I have a number of columns for each room -
the front end for the user.

My problem us that I need to perform a lookup from the diary planner
tab into the customer tab to populate the FIRST INSTANCE of the price.
i.e. When they book further appointments, the value in the diary needs
to be zero.

I've tried sumif, sumifs, counts, vlookups but nothing's working. The
closest I've got is =3DSUM(IF(D6=3D"",0,COUNTIF(\$D\$6:X6,D6)))+(IF(X6=3D"",
0,COUNTIF(\$D\$6:X6,X6)))+(IF(N6=3D"",0,COUNTIF(\$D\$6:X6,N6))) which is
causing circular references for the first 2 rooms. Basically I counted
the number of times "5023Tony Summers" appeared above the current row
(wanted to pick just the unique cells but countif seems to not be able
to pick multiple cells so I had to use the whole area) and if the
result of that formula was 1 then it was the actual booking, not
course time and therefore the vlookup works. So my room 3 is working
peachy!

So to give a further example:
5023Tony Summers books a 100 minute course and will use 10 minutes in
That's the easy bit. Vlookup 5023Tony Summers into the customer tab
and return the 4th column, =A325.

He then books a further 10 minutes for the following day in room 1.
My formula now needs to identify that a previous booking has been made
in order to bring the price =A30 through (i.e. already paid and is now
using up his minutes). Incidentally, I run a countdown of the
available mintues on the diary tab which should help the shop
assistant to know when the customer is running out of course time -
that's working fine!

Of course this is complicated a little by the fact that the customer
may not want a course and there is data validation in the diary for
"walk in" trade which has a different pricing structure and this has
complicated my approach and has meant a frightening amount of nested
formulas... I don't think this affects my problem, just a little more
background info.

By the way, for anyone actually interested, it's a Sunbed shop....

Any suggestions would be great, I just hope this is making some sense!

Cheers,
Tony
```
 0
Summerstone
11/16/2009 11:00:20 AM
excel 39879 articles. 2 followers.

2 Replies
801 Views

Similar Articles

[PageSpeed] 21

```Tony,

Use an IF function to see if the VLOOKUP(....) has already been returned in another cell.  Something
like this, perhaps:

=IF(OtherCell=VLOOKUP(....),10,VLOOKUP(....))

HTH,
Bernie
MS Excel MVP

"Summerstone" <summerstoned@gmail.com> wrote in message
Hi all, I hope someone can help with this one - I think I'm overly
complicating my problem here - hoping there's a simple answer - it's a
tricky one to describe though....

Here's the overview.
3 rooms can be booked for courses where the customer will use minutes
in subsequent visits.
When making a booking, a "customer" tab is used to identify the price
of the couse and gives the customer a unique number e.g. 5023Tony
Summers (concatenated for a lookup)
In the diary planner tab, I have a number of columns for each room -
the front end for the user.

My problem us that I need to perform a lookup from the diary planner
tab into the customer tab to populate the FIRST INSTANCE of the price.
i.e. When they book further appointments, the value in the diary needs
to be zero.

I've tried sumif, sumifs, counts, vlookups but nothing's working. The
closest I've got is =SUM(IF(D6="",0,COUNTIF(\$D\$6:X6,D6)))+(IF(X6="",
0,COUNTIF(\$D\$6:X6,X6)))+(IF(N6="",0,COUNTIF(\$D\$6:X6,N6))) which is
causing circular references for the first 2 rooms. Basically I counted
the number of times "5023Tony Summers" appeared above the current row
(wanted to pick just the unique cells but countif seems to not be able
to pick multiple cells so I had to use the whole area) and if the
result of that formula was 1 then it was the actual booking, not
course time and therefore the vlookup works. So my room 3 is working
peachy!

So to give a further example:
5023Tony Summers books a 100 minute course and will use 10 minutes in
That's the easy bit. Vlookup 5023Tony Summers into the customer tab
and return the 4th column, �25.

He then books a further 10 minutes for the following day in room 1.
My formula now needs to identify that a previous booking has been made
in order to bring the price �0 through (i.e. already paid and is now
using up his minutes). Incidentally, I run a countdown of the
available mintues on the diary tab which should help the shop
assistant to know when the customer is running out of course time -
that's working fine!

Of course this is complicated a little by the fact that the customer
may not want a course and there is data validation in the diary for
"walk in" trade which has a different pricing structure and this has
complicated my approach and has meant a frightening amount of nested
formulas... I don't think this affects my problem, just a little more
background info.

By the way, for anyone actually interested, it's a Sunbed shop....

Any suggestions would be great, I just hope this is making some sense!

Cheers,
Tony

```
 0
Bernie
11/16/2009 5:24:22 PM
`Visit www.ExcelGoodies.Com`
 0
herochenna (62)
12/16/2009 5:00:42 PM

Similar Artilces:

Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...

Vlookup edited
Hello all you wonderfulhelp, Is it possible to avoid "NA" when using "vlookup" function. I need info only where it brings results. Thank you -- smile =IF(ISERROR(VLOOKUP(B1,C1:D4,2)),"",VLOOKUP(B1,C1:D4,2)) -- Gary''s Student - gsnu200851 Example of using ISNA rather than ISERROR which hides all errors. =IF(ISNA(VLOOKUP(G1,\$A\$1:\$F\$31,2,FALSE)),"",VLOOKUP(G1,\$A\$1:\$F\$31,2,FALSE)) Gord Dibben MS Excel MVP On Thu, 7 May 2009 09:26:02 -0700, israel <israel@discussions.microsoft.com> wrote: >Hello all you wonderfulhelp, > >...

Using AutoSum Button results in miscalculations
I have a workbook that has a couple hundred worksheets in it and one o the worksheets is pulling values (via formulas) to it from many of th other sheets. Now, all the values have apparently pulled ove correctly, but when I run an AutoSum totalling all of the values i adds them incorrectly. I verified this by using a calculator and b retyping all the numbers in the next column in the worksheet and usin AutoSum to total those numbers. What could cause this? Is there a rounding problem somewhere? I hav double and triple checked my formulas and they are all correct. I jus don't understa...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

I am having issues calculating XIRR. When my first cash flow is 0, I get an error message, even though a regular IRR calc allows a 0 in the first period. Is this just the way XIRR works or is there a way I can get around it? Thanks for you help. carlsondaniel@gmail.com wrote: > I am having issues calculating XIRR. When my first cash flow is 0, I > get an error message, even though a regular IRR calc allows a 0 in the > first period. Is this just the way XIRR works or is there a way I can > get around it? This appears to be a defect in the XIRR algorithm. As you noted, IRR wo...

I have a spreadsheet that prints out on 3 pages of A4. However, the header prints at the top of each page. How do I get it to print at the top of the first page, only, please? Gareth -- For essential news about Huddersfield Town Football Club join the Terriers Mailing List at www.Terriers.me.uk . Two separate print jobs are required. You can use a macro to speed it up but it is still two print jobs. See Ron de Bruin's site for code. Gord Dibben MS Excel MVP On Sun, 27 Jul 2008 02:46:28 +0100, "Gareth J Dykes" <firstname.lastname@btinternet.com> wrote: >I ...

There was a post almost a month ago on this subject. The question posed was a good one though the answers returned didn't really answer the need. It's easy to move to the right on enter but how do you move to the next row and first column if you are at the end of the first row. Yes one can hit down arrow/home or Home/down arrow but this is a little annoying. If a sheet is protected and cells are locked, isn't there an easier way to do this more elegantly? Thanks in advance for your thoughts, Greg Say you start with A1 selected. Type something in A1 and hit the Tab key...

vlookup problems with left
I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit number (last digit is some kind of control digit) and when i use left to take only the first 9 digits =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE) i get #N/A if i manuallly delete the 10th digit i get mached data. is there a problem with using left on vlookup? =left() returns text. That will never match a real number. I'd take the integer amount after dividing by 10. =vlookup(int(f200/10);w198:w201;1;false) But if you're really only looking to see if there's a match, you could use: ...

(Advance Programming) Parameter Returned as Value
Hi, I wish some body could Help Me, I have being loocking for a solution for 2 days. I need to make a simple Query to check if the parameter values are in a Table. I want the Query to return "Founds" and "Not Founds". For Example: Table Definition: TName CREATE TABLE [dbo].[TName] ( [IdName] [bigint] IDENTITY (0, 1) NOT NULL , [Name] [nvarchar] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ) ON [PRIMARY] Values: Insert Into TName (Name) Values ('Paul') Insert Into TName (Name) Values ('Peter') Insert Into TName (Name) Valu...

From one worksheet to the next, the enter key will only activate the cell directly below the current cell. What Have I done to change the direction? Have checked the direction selection in Options and as suggested by MS Help made sure the Down was selected. Doesn't change a thing. Help! Sandy look in Tools | Options | and on the Edit tab check what's in "Move selection after Enter" Regards Trevor "Sandy" <anonymous@discussions.microsoft.com> wrote in message news:212b01c3e06b\$ac7afe50\$a401280a@phx.gbl... > From one worksheet to the next, the ent...

vlookup help linking data between worksheet
hi, I have a master list of students (about 200+ )in one worksheet. On the succeeding worksheets are the attendance for seminars. We have more than 20 seminars in a year. Because of the large no of attendees per seminar, I usually type out a list of the students who came for each seminar, so there will be 20 +attendance worksheets. Not all students will come every time and there are new ones for each session. To update on master list, I will sort each sheet by surname, print it out and type in separate column (date) for each session and typed "P" for present and &quo...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, \$1000 100, \$1200 200, \$1500 300, \$2000 400, \$2500 500, \$3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs \$1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,\$1000 B,\$1500 C,\$2000 D,\$3000 This would be ...

Parse Name Field into First Name Last Name
I have a field called [Patient Name] that I want to parse out into a First Name Field and Last Name Field. I am using the Charindex() to find the comma that seperates the First and Last Name, I am then passing the Charindex value to the Left Function to grab the Last Name. Below is what the code looks like: LEFT([Patient Name],Charindex(',', [Patient Name])-1) AS LAST_NAME I am getting the below error when using the above code Invalid length parameter passed to the LEFT or SUBSTRING function. Warning: Null value is eliminated by an aggregate or other SET operation. ...

Returned error.
The error message is: 551 Command RCPT User not local and relaying not permitted from you The computer with Outlook is going through a Win2K router. Does anyone no if I need to change any settings on the router or is there a setting in Outlook that I need to make. ...

Return only the numbers contained in a field
Hello, I would like some help please building a query that will return the numbers only contained in a field: Table "PurchaseOrder" has a "Memo" field that usually has the following data: "Sales Order 7540:" or "Sales Order 34:". How can I create a query that will show me all the Sales Orders and their linked Purchase Orders (PurchaseOrder.RefNumber) where the SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo field? Thanks in advance, Brett Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd expect ugly...

Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql server) and it paste the data on 4 sheets in excel. Occasionally the reports are too big for excel. I'd like to develop a similar database that saves the results of the 4 stored procs as tables in a new Access database. right now I have it returning the results as a query. How do I save the results in another database? Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 Hi Billy The following SQL statement should do it for you: SELECT * INTO [TableName] I...

Hide formula, show result
I have a formula typed into a cell. The cell displays the formula not the result of the formula. The options are set to hide formulas. I know this has something to do with cell formats but I don't recall the solution. Please refresh my memory!!! Thanks Could it be that you have: tools|Options|View Tab|Formulas checked? Or do you have that formula cell formatted as Text? DWJ wrote: > > I have a formula typed into a cell. The cell displays > the formula not the result of the formula. > The options are set to hide formulas. > I know this has something to do with cel...

Socket probem,GetLastError returns 10038 (Invalid socket handle)
hi all I am implementing SMTP protocol in MFC using Sockets. I am using the CSMTPConnection v1.36 code provided by PJ Naughter at http://www.codeproject.com/internet/csmtpconn.asp The problem is while sending mails sometimes mails are not sent. 1)I am connecting ,sending mail and then disconnecting. The above step is repeated 6 times hence 6 mails should be sent.Sometimes only 4 or 5 mails are sent.But sometimes all 6 mails are sent!!! The problem seems to be while Sending the MAIL command in SendMessage()function The return value of GetLastError() was 10038 i.e Socket operation on ...

If the result of a formula is negative make equal to zero
I do have a worksheet that calculates sales profits or losses quarterly in 3 different locations. For each quarter I do have a formula that gives me the increase or decrease amounts during the last couple of years. For the total result I need a formula that converts or ignores the negative results. Any calculation result with a minus sign in the formula should be equal to zero, so it's not included in the final quaterly total. Could somebody help me with it? Thanks! =IF(yourformula<0,0,yourformula) Regards Trevor "Nrippe" <Nrippe@discussions.microsoft.com> wro...

Exporting of Search results to Excel
Need to know if results of search/filter can be exported to Excel without formal report being needed. I.e., in SQL you can save a search/query and input the "data" you want to search on at any time, does Access have the same capability? As an end user of an access database I can search and then filter the database but am told I cannot download the results of that search/filter to Excel without having a formal report. If yes, please advise how I can relate this requirement to an Access programmer. Thanks -- EU Create a query for the purpose of exporting. Write the SQL prop...

VLookup?????
=IF(ISNA(VLOOKUP(B2,'TableB'!\$Y\$2:\$Y\$100,1,FALSE)), "",Column A in tableB) This is the code i have. I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B. Also if there is 2 occurences of column B in table B, i want the field in table A to show both values. Any ideas? (if u can understand what im asking) -- Hazy ------------------------------------------------------------------------ Hazy's Profile: http://www.excelforum.com/member.php?action=g...

Final results
I'm a bit new to Access and trying to find my way around it. I've created my tables and forms etc and i'm now trying to create reports from the data that is now in the table. I've created a query that does some calculations for me but i'm struggling to get the information into the format / final results i want. The query looks a bit like this:- Date Name1 Name2 Name3 Name4 Name5 Name6 Name7 Name8 Rate 10/5 bob mike jim 10 10/5 mike bob ...

Looking for an envelope template in pub w/return address on back
I would like to add my return address on the back of the envelope in Publisher. Does anyone have a template? ...

Visual C++ 6.0 CDialog::Create fails (returns 0)
I don't understand what could cause this? Pass in a valid ID and the current object (this). if( !m_pControlDlg ) { m_pControlDlg = new CControlDlg(); ASSERT( m_pControlDlg ); ASSERT( AfxIsValidAddress( m_pControlDlg , sizeof( CControlDlg ) ) ); bool bCreated = m_pControlDlg->Create( IDD_DIALOG_CONTROL, this ); ASSERT(bCreated);////this is where my failure occures m_pControlDlg->ShowWindow( SW_SHOWNORMAL ); } Any ideas? I appreciate any ideas you could give. Thanks. "Kman" <kman_l@yahoo.com> wrote in message news:1190846202.092456.23310@g4g2000h...

RMA should aloow returning of non-inventory items (defect sku)
Returning an non-inventory item (defect sku) Is there any way to enter an RMA on a service, misc., or flat fee item? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=805d90...