returning vlookup values for blank cells

I have a spreadsheet that lists "soccer players" by name 
down the first colunm and "time in game" across the top 
and the position they play in array.

I then use vlookup for another spreedsheet by "position" 
down the first column, time across the top and puts the 
players name into the positions.  All this works fine.

Since there are 5 more kids than positions, the orginal 
spreedsheet has blanks when the kids are out of the game.

How do I use vlookup or other to extract the 5 sub'd out 
kids at the bottom of the 2nd spreadsheet?  It only 
returns the name associated with the 1st blank.


0
10/8/2003 12:26:21 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
555 Views

Similar Articles

[PageSpeed] 5

"Craig McCormick" <craig.mccormick@netzero.net> wrote in message
news:10fe01c38d32$cc722620$a001280a@phx.gbl...
> I have a spreadsheet that lists "soccer players" by name
> down the first colunm and "time in game" across the top
> and the position they play in array.
>
> I then use vlookup for another spreedsheet by "position"
> down the first column, time across the top and puts the
> players name into the positions.  All this works fine.
>
> Since there are 5 more kids than positions, the orginal
> spreedsheet has blanks when the kids are out of the game.
>
> How do I use vlookup or other to extract the 5 sub'd out
> kids at the bottom of the 2nd spreadsheet?  It only
> returns the name associated with the 1st blank.
>
>

Take a look here, under Arbitrary Lookups:
http://www.cpearson.com/excel/lookups.htm


0
Paul
10/8/2003 7:44:56 AM
Use 1,2,3 or more spaces instead of blanks - VLOOKUP() will find them correctly.

Regards
BrianB
-----------------------------------------------------


"Craig McCormick" <craig.mccormick@netzero.net> wrote in message news:<10fe01c38d32$cc722620$a001280a@phx.gbl>...
> I have a spreadsheet that lists "soccer players" by name 
> down the first colunm and "time in game" across the top 
> and the position they play in array.
> 
> I then use vlookup for another spreedsheet by "position" 
> down the first column, time across the top and puts the 
> players name into the positions.  All this works fine.
> 
> Since there are 5 more kids than positions, the orginal 
> spreedsheet has blanks when the kids are out of the game.
> 
> How do I use vlookup or other to extract the 5 sub'd out 
> kids at the bottom of the 2nd spreadsheet?  It only 
> returns the name associated with the 1st blank.
0
Brian.anon (77)
10/8/2003 11:42:25 AM
Thanks for both inputs.  Both work.

Paul, yours was what I needed.  Worked perfectly.

Thanks...Craig


>-----Original Message-----
>I have a spreadsheet that lists "soccer players" by name 
>down the first colunm and "time in game" across the top 
>and the position they play in array.
>
>I then use vlookup for another spreedsheet by "position" 
>down the first column, time across the top and puts the 
>players name into the positions.  All this works fine.
>
>Since there are 5 more kids than positions, the orginal 
>spreedsheet has blanks when the kids are out of the game.
>
>How do I use vlookup or other to extract the 5 sub'd out 
>kids at the bottom of the 2nd spreadsheet?  It only 
>returns the name associated with the 1st blank.
>
>
>.
>
0
craig9605 (1)
10/13/2003 6:23:29 PM
Reply:

Similar Artilces:

Opening to a specific cell
Can you please tell me how to go about opening a spreadsheet at a specific point within. I am keeping a timesheet and don't want to have to scroll through 11 months to get to December. How can I get the spreadsheet to open in December? Thanks for the help! matt Save it in December before you close, or enter code in the Workbook_Open event, such as: Range("December").Select where you have a range (one or more cells) named December. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "matt" <anonymous@discussions.microsoft.com> wrote in messa...

Value filter missing in pivot table
I have a spreadsheet that totals sales and commisions based upon a date range. Basically I've got two worksheets. One with the raw data, and another with the pivot table based upon the raw data. I've been unable to validate my totals as compared to other established reports. After digging into the report, I see that I'm missing 1 specific sales associate's data in my pivot table. I can clearly see a lot of data for this associate in the raw data sheet. While in the pivot table, I can go to the row label, and value filter, but this one sales associate is not ...

Equation and Tex in Same Cell
Good Morning! Is there a way to add text to an equation in the same cell? For instance =now() Fee Due Thank You! Wayne wgd.roaming@verizon.net wrote: > Good Morning! > > Is there a way to add text to an equation in the same cell? > > For instance =now() Fee Due > > Thank You! > > Wayne =TEXT(NOW(),"mm/dd/yyyy") & " Fee Due" SMarton: Perfect. Thank You! On Sat, 27 Jun 2009 15:55:02 -0400, smartin <smartin108@gmail.com> wrote: >wgd.roaming@verizon.net wrote: >> Good Morning! >> >> Is there a way to...

Outlook 2003 error "The messaging interface has returned an unknown error if the problem persists restart outlook" <1171052160.017239.244810@j27g2000cwj.googlegroups.com>
I have seen some posts in and around which points some issues with Add-Ins. Remove all non native Outlook Add-Ins, restart OL and see if it solves your problem. Goto Tools > Options > Other tab > Advanced Options > AddIn Manager and remove all non native AddIns. See if it can help you. Thanks! dgoyani http://dgoyani.blogspot.com/ EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com ...

Return top level folder list and respective sizes from current fol
Hi All, I'm trying to create a simple script that will return a list of folders in the current directory and their respective sizes: Set objShell = CreateObject("WScript.Shell") Set objFSO = CreateObject("Scripting.FileSystemObject") Set strCurrentDirectory = objFSO.GetParentFolderName(Wscript.ScriptFullName) Set objFolder = objFSO.GetFolder("strCurrentDirectory") Set colSubfolders = objFolder.Subfolders For Each objSubfolder in colSubfolders Wscript.Echo objSubfolder.Name, objSubfolder.Size Next Yet it doesn't like the fact that ...

Disappearing cell values in Excel 2007
Hello all, I've recently been experiencing an issue where as I scroll through a spreadsheet rows of cell values will appear to be blank. When I select the cell, the value is still present in the formula bar but only reappears when I double click on the cell or scroll either up or down so that the cell is not shown and then scroll back to the cell. I've experienced this is file ranging in size from 400-700 KB and there doesn't seem to be a pattern as to what cells or rows it will affect at any given time. Is this most likely a memory issue? Thank you, Andrea On Thu, 5 Mar 2009 ...

Random Locked cells
I am having a problem with a spreadsheet that has random cells locked. The only way to unlock them is to copy and paste a blank cell that isn't locked into the cell that is locked, but this doesn't always work either Has anyone had this type of problem? Any ideas Thanks Leonar lmack@usccs.co Hi Leonard Maybe I don't understand you correct but if you select a cell or cells and right click on it and choose Format Cells you can change the locked on the Protection Tab -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Leonard Mack" <lmac...

RPC over HTTP setup help. RPC DIag returns no results.
I have been trying for several weeks to get RPC over HTTP workign in our environment. I refuse to put an ISA in place and am currently behing a Pix 520 ( which is not the issue, all logs show no denied traffic) I also see no error logs in either of my Exchange servers. Please any help is appreciated. From my Front end Server I see the followign when running RPCDIAG ** Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp. C:\Program Files\Windows Resource Kits\Tools>rpcdump /p ncacn_http Querying Endpoint Mapper Database... RpcMgmtEpEltInqNext:(Access is denied....

shortcut key for cell concatenation
is there any shortcut key for cell concatenation? if there's none, then is there any way to create shortcut keys on your own? You can write a macro and assign a short cut key to it. -- Regards, Tom Ogilvy hal9000 <anonymous@discussions.microsoft.com> wrote in message news:026801c3c2b7$81b592b0$a401280a@phx.gbl... > is there any shortcut key for cell concatenation? > if there's none, then is there any way to create > shortcut keys on your own? would you tell me the macro for cell concatenation? i mean i'd like to merge cell a1 and cell b1 and make it as a lar...

MS Query returning incomplete results
Hi, Have been using Excel Queries for a while with no problems. Since last week, queries are coming back incomplete, returning blanks for cells that should have data. Have imported the data into Access and the queries work properly. Any suggestions ? Thanks, Mike ...

email cell phone not going thru
We have used our contact list to email our members for a long time. We upgraded to Office XP, now when I send messages to my verizon cell @ vtext.com it doesn't go thru. It works from their web site. There is a setting or something in Outlook that will not allow the message to go. Can anyone help? What exactly happens when you try to send to this address? -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Santa Fe" <anonymous@discussions.microsoft.com>...

How do I refer to the tab name in a cell formula in Excel?
I'd like to refer to a tab name automatically in a cell formula, just like you can autoinsert it as in the page header. Is this possible? The following formula will return the tab name. =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Steven Reames" <StevenReames@discussions.microsoft.com> wrote in message news:0E63B2E9-628B-481D-A6AF-AA362C649E99@microsoft.com... > I'd like to refer to a tab name automatically in a c...

How to remove the cell reference in a sheet at once
Hi, I have a sheet where there are many cells are fix (reference cells) using the F4 key. Is there any way i can remove the effect of the F4 without going to each formula and them keep pressing F4 to remove the reference??? With formulas like =$A$1+$B$1 and pulled down for 30 cells, I used Edit > Find > $ > replace with "nothing" > OK HTH Regards, Howard "The Greek" <izzalzurba@gmail.com> wrote in message news:c97e7e60-2fc9-44e6-8411-977ad6c7f3fb@v29g2000prb.googlegroups.com... > Hi, > > I have a sheet where there are many ...

Lining Scale to Merged Cells
Is there a way to set the Y axis minimum and maximum using a link to a merged cell range? Example: Cell Range M20:O29 Value entered 65% Cell Range M15:O15 Value entered 115% I want to be able to change the scale in the worksheet without having to format the scale in the chart itself. Would this be done with VBA in the worksheet module where the chart is imbedded? Thanks, Phil Hi Phil, There is no in built way to do this. But have a look at Jon's page, which also includes a reference to Tushar's Auto chart manager. (http://peltiertech.co...

How ger a value of the one sheet based in a text in other sheet.
I have two excel workbooks with different data, but a column with the same information in them exists. As I obtain a value of one of books basing to me on the value thatexists in the common column? It sounds like =vlookup() or =index(match()) would work ok. Visit Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html for nice instructions. Carcastel wrote: > > I have two excel workbooks with different data, but a column with the same > information in them exists. As I obtain a value of one of books basing to me > on the value thatexists in the common column...

Can an IIf Statement return more than one value
I have a form that has two cascading combo boxes on it. I would like to have a third combo box that is populated based on the data entered in the two combo boxes. Here is my current layout The two cascading combos are “cboModel” and “cboHousing”. The third combobox is “cboStroke” for its row source I was just using an IIf Statement and that works but I can only get it to return one value and I would like to have up to three or more values in combobox. Here is what I have tried so far. Stroke: IIf(Forms!frmQuote!SubfrmQuote!cboModel=1,24, IIf(Forms!frmQuote!SubfrmQuote!cboModel=1 And ...

Option on PO returns to add items to purchase order
When entering PO returns with no credit it would be nice to have the option of either adding the returned items back to the original po or to create a new PO. This type of return is often used to correct receiving errors , or to return defective material for replacement. -- Jim@TurboChef ---------------- 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-bas...

How to assign a value to memory pointed by an IntPtr
Hi, In an earlier post, I was wondering why I can't compile unsafe code *EVEN* when the, "Allow unsafe code," check box is checked. I'm still working on that one. However, the whole problem can be averted if someone here can explain how I might assign a value to memory pointed to by an IntPtr object. Using the language that I know (C++) to illustrate, this is what I want to do with an IntPtr object: int *pInt = new int; *pInt = 5; My C# code is: IntPtr dataSize = Marshal.AllocHGlobal(sizeof(int)); dataSize = ??????? So how do I assign a value to the ...

cell references in excel
I have read that when you insert a row directly above a formula, the row is not automatically updated in the cell reference, but I was wondering if there was a way around this. For example, I have a spreadsheet and it calculates the sum of all the rows above the totals row. When I add a new row directly above the totals row, it does not include this new row in the totals. Unfortunately, I have to add the new row directly above the totals row, because the rows are in birthdate order. Any suggestions??? The "easiest" way for you would be leave a blank (hide if you like) to ...

Date issue to runover into the next blank cell
Why does't my date run over into the next blank cell?? When I have a text is runs over the next blank cell, when I have a date in a cell not big enough?? Any suggestions? Because dates are numbers and they will return ##### if the cell size isn't enough -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gmick" <gnick@chello.nl> wrote in message news:yeJRc.132$mg.81@amsnews02.chello.com... > Why does't my date run over into the next blank cell?? When I have a > text is runs over ...

zero value removes columns. How do I keep columns with zero value
..I have a workbook that run in Business Warehouse. I enter a date range, and several columns fill in. However, if one of the columns has a vlue of zero, the column will not display. Ex a workbook has col_a, col_b, col_c, col_d. If col_c has no data, the worksheet will only show, a, b, and d. I need the zero value in column D because I have chart data linked to the worksheet. If col_c does not display, my chart is wrong: the data is returned as column a,b,d, and my chart is looking for a,b,c,d. So d's data goes into col_c. I tried telling excel to show zero values. I fooled around ...

One or more rows contain values violating non-null, unique, or foreign-key constraints
Populating a typed dataset from xml document: I created an xml schema (attached below), generated a typed dataset from it, and then programatically I tried to populate the typed dataset by calling its ReadXml method. I keep getting a constraint exception. I have validated that my xml matches the schema using xmlspy. I've included the schema, xml, code and exception information below. I can't figure out why I would get the exception if my xml validates against the schema, and the dataset was generated from the same schema. Exception--------------------------------------------...

Predefined cells
I want to set up an excel spreadsheet in such a way that, for example, in column A, users can only enter USA and FRN. I will receive this file from a lot of people and the consistency of data entry will make my consolidation easier. I know there is a way to have a drop down box (or something similar) that I can predefined the choices of value for those cells. Users can only choose in those cells the values that I already defined. I just do not know how to do that. Wonder if anyone can help. Use Data Validation. In Allow, select List, and type your values in there. For more i...

Count unique values
Hi In my Excel worksheet there are two columns A & B. A contains the product codes and B contains the customer codes. How can I count the number of UNIQUE customer for a particular product? Both formula or pivot table would be fine. Col A Col B Pen Customer 1 Ruler Customer 1 Pen Customer 2 Ruler Customer 1 Pen Customer 1 Pen Customer 3 Thanks in advance! Try the below array formula. Apply formula using 'Ctrl+Shift+Enter' instead of 'Enter' =SUM(IF(FREQUENCY(IF((B1:B10<>"")*(A1:A10="Pen"), MATCH(B1:B10,B...

Finding blank cells
Hello I want to find the first and second blank cells in a column and return both their values as variables I can then program with. Anyone know how? I got as far as: If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row ' Cells.FindNext(After:=ActiveCell).Activate End If which returns just the first blank cell but as a messagebox. Any help much appreciated A way that works if there are actually two blank cells... '-- Sub FirstTwoBlanksOnly() Dim rOne As Range Dim rTwo As Range Dim rng As Ra...