Vlookup - return row no. instead of value

Dear all,

For vlookup, it will return the value of the destination cell. Is it
possible to return the row no. instead of value of the destination cell??

Your help is highly appreciated.

Thanks & regards,
Automne


0
3/4/2005 5:41:39 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
452 Views

Similar Articles

[PageSpeed] 12

Look at the MATCH function in Help.

On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk>
wrote:

>Dear all,
>
>For vlookup, it will return the value of the destination cell. Is it
>possible to return the row no. instead of value of the destination cell??
>
>Your help is highly appreciated.
>
>Thanks & regards,
>Automne
>

0
anonymous (74722)
3/6/2005 11:35:16 PM
On Sun, 06 Mar 2005 17:35:16 -0600, Myrna Larson
<anonymous@discussions.microsoft.com> wrote:

>Look at the MATCH function in Help.

or add another column with the row number in it ...

>On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk>
>wrote:
>
>>Dear all,
>>
>>For vlookup, it will return the value of the destination cell. Is it
>>possible to return the row no. instead of value of the destination cell??
>>
>>Your help is highly appreciated.
>>
>>Thanks & regards,
>>Automne
>>

0
me7986 (249)
3/7/2005 12:38:58 AM
Reply:

Similar Artilces:

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Subtract several rows from a total
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C53AC8.AD6EB0F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Guys, I have a total in cell C32. I want to subtract numbers in cells C 34-40 = and have the answer in cell C41. I know that =3DC32-C34 will give me an = answer but I want several rows to be subtracted. What would my formula = be? I have used the insert function feature before but I don't see diff or = anything that looks like a subtraction function in there. =20 Thanks, Linda ------=_NextPa...

Deleting rows containing common data
I have an Excel 2000 spreadsheet with 30,000 rows. The rows look like this: 007-007 68-4611 68-4682 total 007-007 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 68-4682 total 007-007 68-4682 total How do I delete the rows containing "total" One way: 1. Select the range and apply an AutoFilter (Data > Filter > AutoFilter) 2. On the drop-down list, select "Total". 3. Select the range again if needed. 5. Press F5 > Special and click "Visible Cells Only." 6. Remove...

Ending balance doesn't equal market value
I am using MS Money 2004. In my portfolio the Contributions ($) Market Value amount is showing a negative amount, but Today's Balance shows a zero Ending Balance. How do I get them to both show tha same number? In microsoft.public.money, Eggman wrote: >I am using MS Money 2004. In my portfolio the Contributions ($) Market Value >amount is showing a negative amount, but Today's Balance shows a zero Ending >Balance. How do I get them to both show tha same number? Try File->RepairMoneyFile->QuickFileRepair Look for future transactions. ...

Returning Partial Strings
I'm trying to separate a word/string of characters in a text field from the rest of the field, but the strings are of varying lengths. Is there a way to extract this? For example, my field contains the following values: RAST 2006-1 M5 CWALT 2006-AR1 A How can I extract the "RAST" and the "CWALT" from each? How can I extract the "2006-1" and the "2006-AR1" from each? Thanks in advance! On Thu, 31 Jan 2008 14:44:02 -0800, ScottM wrote: > I'm trying to separate a word/string of characters in a text field from the > rest of the field,...

Lowest value in a selection of data
Hi What formula would I use to find the lowest value in a selection of data i.e. B49:E52. Much Appreciated Brian =MIN(B49:E52) -- Gary''s Student - gsnu2007a Jumping in here... Is it possible to know the address of the cell containing the minimum value? John. You can use the MATCH function to locate the (relative) position, but if you have more than one value which are minima then it will only find the first. Pete On Nov 16, 7:23 pm, John Google <JohnGoo...@hotmail.co.uk> wrote: > Jumping in here... > > Is it possible to know the address of the cell containin...

Help with vlookup #3
using 2003. I am looking up a number (stored as a string) on another worksheet and returning another series of numbers (also stored as a string) - ie lookup return 411140 263791-411140E Problem - Excel formats the returned data as: 2.63791E+11. It apparently sees it as a mathmatical subtraction of an exponential number even tho it is a text. It doesn't seem to matter how I format the cell. When I format it as text, the formula does not work. (requires General to execute formula). Executing a "text to columns" does the same thing. I need the returned data to show - 263...

Adding Values From Different Tabs
Is there a way to add values from different tabs on the same spreadsheet? For example A2 from tab 1 and A2 from tab 2 -- Flipkid2 ------------------------------------------------------------------------ Flipkid2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17984 View this thread: http://www.excelforum.com/showthread.php?threadid=398184 Assuming the name on Tab 1 is Sheet1 and Tab 2 is Sheet2, use thi formula: =Sheet1!A2+Sheet2!A2 note the name of the sheet is followed by "!" to indicate it is a shee name. HTH Bruc -- swatsp0 ------------...

characters in 1 col, 1 per row
Hi all, I want to transform the text of a worddocument to Excel in a special way. In my case text is just a number of words in a number of lines (nothing special about headers, footers etc.), so something comparable with this post. I want in Excel the first charachter of the text in A1, the second in A2 and so on. This email document would result in: H i a l l , I w a n t etc. I can't figure out how to do this, your help wil be appreciated. Jack Sons The Netherlands ...

Display only duplicate values and delete UNIQUE Items
All I have a very large list of data and on a monthly basis i need to display only the duplicate items in a spreadsheet. I would like to do this in VBA and then run it as a macro on the spreadsheet. Alot of the sites that i have seen only show how to removed the duplicates. Excel 2007 has a function which removed all duplicates but so far i have found nothing that only displays the duplciates.... any ideas anyone? Assuming that the field you use to determine uniqueness is column A, you can put this formula in a helper column: =3DIF(COUNTIF(A:A,A2)>1,"Duplicate","Unique&...

Automatic Insert Rows VBA
A Very Good Evening All, I have a macro that runs: advanced filter/copy to another place/unique, between two worksheets (Column A in each worksheet being the identfier; worksheet1 is source data,with duplicates of identifiers; worksheet2 is formula driven from the sum of the unique identifiers in Worksheet1) However...I would like the macro to automatically extend the rows in worksheet2, to accommodate the ever increasing rows of worksheet1 Here is my specific code worksheet1 = "Invoice Record" worksheet2 = "General Report" Sub CommandButton1_Click() ' A...

copy a file to all users via a script instead of manually
I have a d:\users folder on my server. inside users, each user has a folder named with their name. inside each of those folders is a desktop folder. I have a file on the C drive that I need to copy to each of those desktop folders, thereby giving each user this shortcut. Does anyone have a script that could do that, instead of me copying this file manually a few hundred times? many thanks if you do! "Rich" <richjone@noemail.noemail> wrote in message news:5E68A580-FA4D-4B73-A548-0ED9D74AF34C@microsoft.com... > I have a d:\users folder on my server. inside...

VLOOKUP with wild cards urgent!!
Lets say i have two sheets Sheet1 Pradeep Sheet 2 deep in sheet2 i wrote the formula =VLOOKUP("*deep*",Sheet1!A:A,1,TRUE), i was expecting Pradeep, but it gave me #N/A. How correct am i? thanks If you look in HELP for the specifications of VLOOKUP, you'll find nothing about wildcards. They are not implemented. -- Kind Regards, Niek Otten Microsoft MVP - Excel "J T" <agarwalp@lucent.com> wrote in message news:OwLhgG0tEHA.3932@TK2MSFTNGP10.phx.gbl... > Lets say i have two sheets > Sheet1 > > Pradeep > > > Sheet 2 > deep > &...

Some macro help
Lets say I have selected the arbitray range of j31:ax44. I want to run some macro code that will hide every column in that selection where the entire selected column range has nothing in it (blank). Data validation may be assigned to the cells, but if nothing has been selected from the drop down list, then those cells are technically blank. There may be data entered elsewhere in the column, but it is outside of the selected range and thus shouldn't be considered for being hidden or not. Can anyone help me with some code to perform this action? TIA, AlanN How about: Option Explicit Sub...

Exclude like values with a JOIN?
I have the following query: SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD FROM dbo.qry_UV_CARS_PlateX_Valued RIGHT OUTER JOIN dbo.qry_UV_CARS_PlateX_ShouldBeValued ON dbo.qry_UV_CARS_PlateX_Valued.Yl_iD = dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL) And I have another secondary table containing the same field as Yl_iD. What I want to do is have my main query exclude all the Yl_iD values that are in my secondary table. What JOIN and criteria will do this? Many thanks.....Jason WHERE Yl_iD N...

increment a value by 1
How do I increment, automatically, a cell by 1 if a condition is met? If one of my cells reaches 10k I want another cell to increment by 1. Can anyone help me if this function can be done? Thank you, Ron Do you want the increment only when it changes from below 10K to above 10K, or anytime is is above 10K? -- Gary's Student "Workshops" wrote: > How do I increment, automatically, a cell by 1 if a condition is met? If one > of my cells reaches 10k I want another cell to increment by 1. Can anyone > help me if this function can be done? > Thank you, Ron Increm...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...

multiply value based on quantity
I am trying to calculate a discount price based on quantity purchased. For example if someone buys 20 widgets they pay $4.00 each for them, if they buy 50 widgets they pay $4.00 each for the first 20, then $3.50 each for the next 30, 100 widgets - $4.00 first 20, $3.50 next 30 and $3.25 for the other 50.............. Any hope? Here's a way with quantity in A2: =MIN(A2,20)*4+MAX(MIN(A2-20,30),0)*3.5+MAX(MIN(A2-50,50),0)*3.25 Regards, Per "Bigfoot3910" <Bigfoot3910@discussions.microsoft.com> skrev i meddelelsen news:524D35AE-4524-46A3-A963-703CB385C7F...

Macro: Find matching DATA, copy correspondently values into another list
Okey guys - please advise me on this. (There was unfortunately no quick response to my latest problem - and I begun to swim... However - that brought me out on deep VBA-water...again - and now need a life buoy (metaphorical spoken)). The core of my macro problem is to look for all the values in column A (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in another list placed in column A in sheet "LIST". When finding the match I will copy correspondent celle.value in column D / sheet "BOARD" into column B in sheet "LIST" - this w...

internal support function returned an error
Hello, For some reason, every time I try to send a particular message I get "An internal support function returned an error" warning. Any idea what I can do to fix this problem? -- Thanks, Bob Sorry, I forgot to indicate I am using Outlook 2007 on XPSP2. "BBran" <bbran@nowhere.com> wrote in message news:elIab3XhJHA.760@TK2MSFTNGP03.phx.gbl... > Hello, > > For some reason, every time I try to send a particular message I get "An > internal support function returned an error" warning. Any idea what I can > do to fix this problem? &...

make dropdown list greater than 8 rows
Hello How can I make my dropdown list 12 rows instead of only 8? thanks for any help For a Forms Combo Box right click and choose Format Control-Control tab For an ActiveX ComboBox right click and choose Properties and adjust ListRows. -- Regards Dave Hawley www.ozgrid.com "SteveZmyname" <SteveZmyname@discussions.microsoft.com> wrote in message news:105058EA-1275-417C-B4BC-4023DFDAC16C@microsoft.com... > Hello > How can I make my dropdown list 12 rows instead of only 8? > thanks for any help I can change the drop down lines to 12 in the F...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Delete color row
Hi, After I compare two worksheets (VB as below) and highlight the same value with colour, now I want to delete the same value row. The comparison VB that I have worked: Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(New,H5)=1" Selection.FormatConditions(1).Interior.ColorIndex = 6 How do I write VB to delete color row? Raymond ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ You can't programmatically check for a color produc...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...