vlookup function to return the cell address of the found item

I have a one column list of data (around 3,000 items) - and I am using
the vlookup function to determine if an item is in that list using
something like the formula below:

=if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list")

I would like to know if I can have this function return the cell
address or row number to indicate the location of the item in the list
- is this possible?

Thank you for your time and assistance
0
trpayne (16)
7/9/2008 2:48:34 PM
excel 39879 articles. 2 followers. Follow

2 Replies
485 Views

Similar Articles

[PageSpeed] 49

You can return the (relative) row number using MATCH, like this:

=if(iserror(vlookup(A1,D1:D3000,1,false)),"not in 
list",MATCH(A1,D1:D3000,0))

As your range starts with D1, the relative row is the same as the actual 
row, but if your range started with D10, for example, you would have to add 
9 on to the result to get the actual row.

Hope this helps.

Pete


"skiing" <trpayne@chatt.com> wrote in message 
news:b63fa45e-fa88-40e9-b260-3130dedd1f24@d77g2000hsb.googlegroups.com...
>I have a one column list of data (around 3,000 items) - and I am using
> the vlookup function to determine if an item is in that list using
> something like the formula below:
>
> =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list")
>
> I would like to know if I can have this function return the cell
> address or row number to indicate the location of the item in the list
> - is this possible?
>
> Thank you for your time and assistance 


0
pashurst (2576)
7/9/2008 3:25:46 PM
Look in the help index for MATCH

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"skiing" <trpayne@chatt.com> wrote in message 
news:b63fa45e-fa88-40e9-b260-3130dedd1f24@d77g2000hsb.googlegroups.com...
>I have a one column list of data (around 3,000 items) - and I am using
> the vlookup function to determine if an item is in that list using
> something like the formula below:
>
> =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list")
>
> I would like to know if I can have this function return the cell
> address or row number to indicate the location of the item in the list
> - is this possible?
>
> Thank you for your time and assistance 

0
dguillett1 (2487)
7/9/2008 3:27:22 PM
Reply:

Similar Artilces:

Address bar
Is it possible to get the address bar in an explorer window in Vista, ala XP? Or am I stuck with the sideways thing? I've looked under the organize menu, tweaked the folder settings, I'm using the classic theme...Is it possible & if so, how? Thx, Ripper "RipperT @nOsPaM.nEt>" <<RiPpErT> wrote in message news:OKyGhB9ZKHA.4932@TK2MSFTNGP02.phx.gbl... > Is it possible to get the address bar in an explorer window in Vista, ala > XP? Or am I stuck with the sideways thing? I've looked under the organize > menu, tweaked the folder s...

scan the same item more than once
It possible to scan the same item more than once without creating a new line? =?Utf-8?B?RURXSU4=?= <EDWIN@discussions.microsoft.com> wrote in news:10DB4D57-4770-4947-B45A-B41A7B9B7D5D@microsoft.com: > It possible to scan the same item more than once without creating a new > line? > > > No, when you scan an item it is just going to be added to the next line. You can set the quantity BEFORE you can the item using the "*" key... 4 * <scan barcode ot enter item lookup code> The above sequence will set the quantity to 4... You can also set the...

How to select a sheet and input data into certain cells
I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT AA2=2004 Sheet2 Sheet3 etc . . . AA3=2005 Sheet6 Sheet AA4=2006 Sheet8 Sheet AA5=2007 Sheet4 Sheet I would like to be able to select a year and a quarter and it goes to the sheet, i.e 2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter I would also like to be able to print sheet5 and ask me if I want to print it agai or close and upon closing go back to sheet Is there an...

Need to block email going to external email address from inside co
I have users sending info to unauthorized email address out side the company but to the best of my knowledge I have no way of blocking this. I know I can block emails going to an internal user but how do I block internal emails from going to external address? External being SMTP Enviroment is Win2003 Exchange 2003 OutLook 2000/2003 I don't have access to my exchange server at the moment so I can't check this but I think that you can filter the email domain within exchange server so that if any mail going to the external address is sent, it gets blocked before it leaves the ex...

Delete Global Address entries
I'm the network admin over a Windows 2000 domain of 20 client XP workstations. We are running Exchange server and AD. Two of the employees recently left the company and now they don't want their email addresses to show up on the Global Address List. But they don't want to delete their email boxes yet either, just in case they want to view their old emails. Is there a way to remove their email addresses from the GAL without deleting their mailboxes? Thanks. Open active Directory U&C Set to advanced view mode double tap the user you are interested in. Go to Exchange ...

Inserting extra info into a cell
I currently have three columns in a spreadsheet. This spreadsheet is used as a bilingual glossary of terms. The first column simply contains the following: ** Now the second column contains my Spanish words and the third column my English words. So, it looks something like this:- ** Hola Hello Now, I wish to import this data into some new software I have however, it requires me to put the word <Spanish> before every words in the second column and <English> before every word in the third column. So, in essence, it will be ** <Spanish>...

How do I add a search function?
Hi everyone, ('scuse my English please I'm still learning) I'm not to good at Excel and I'm working on our mock assignment at the moment what I wanted to know is that how can I search from all the informatio that I have stored. I've made a form and I'm going to add macro wic will make it store the information on a let's say sheet3 which i hidden, I also want to add a sheet called Search which will allow me t search for what I type in the field for example by name, or parce number.. etc.. Or do I have to use Lookup formula for that? I'm lost! don't know if ...

Lookup function 02-14-10
I have excel 2003. I did a vlookup for my transcripts page. In the first vlookup i needed it to look for the letter grade and give me the gradepoint average. Now in the other cell i need it to look for the grade. In the cell that gives the gradepoint average it has a formula in their already. Can i still do a vlookup if the cell that has the information has a formula in it already. Example. The cell that has the formula adds all the grades from each class and gives me an accumluated gradepoint average for the ten classes taken. Now that gradepoint average I want a letter ...

HTML e-mail to a list of addresses
When I prepare an e-mail in HTML and attempt to send it to a list of addresses, I have to approve each one, but when I check Outlook outbox, it says "HTML", but the content is blank. If I send a an e-mail to a single address, it is in sent HTML format and is sent properly. Why can't I send an HTML e-mail to a list? what version of outlook and what are the exact steps you are doing to compose and address the mail? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: ht...

Specifying next cell to "jump" to
When a user completes as much of a specified cell range as necessary an wishes to move on to the next range, I would like to give them a option to go "automatically" to the first cell of the next range a opposed to having to tab through the remainder of the range or click i the first cell of the range they wish to go to. For example, the firs range is A10 through D25. After inputting data in D15, the user want to go to the next range, the first cell of which is A30. How can th user most quickly and easily go from D15 to A30? I appreciate your help -- Message posted from http://...

Search Function Disabled (Partly)
Dear All, I've got a friend of mind has asked me why his Search function from Right Click and from Start Menu has stopped working, if he navigates to a folder and clicks search from the top bar, he's able to search, but not from any other way. I had a little search around (via google) and any results I'd turned up, like Kellys corner, etc, none of those registry fixes seem to work.. I'm a tad confused why only part of the search function is disabled. Can anybody help Regards Paul. In news:%236cW6lfzKHA.5036@TK2MSFTNGP02.phx.gbl, Kardon Coupi <pre...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

Locking certain cells
I have an expense reporting template which I have locked certain cells that I do not want changed while allowing input into other cells. This is under the tools functions but its not coming to me right away how I did this. The users do not need to use any passwords --its just certain cells are read-only and cannot be changed unless unlocked. How'd I do this I need to update? Disregard- figured it out again "Scott" wrote: > I have an expense reporting template which I have locked certain cells that I > do not want changed while allowing input into other cells. Th...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

Help - RMS 2.0 Installation Problem
Hello, I just installed RMS 2.0, and the"Add Item" button does not work in the Purchase Order. I click on it, and nothing happens. I have downloaded RMS 2.0 Service Pack #2, but have not installed it yet. Thanks, Greg That problem is addressed in one of the older hot fixes prior to SP2. Marc "gregfrgusa" <gregfrgusa@discussions.microsoft.com> wrote in message news:EEC0B25E-695E-4154-B770-398280F40F08@microsoft.com... > Hello, > > I just installed RMS 2.0, and the"Add Item" button does not work in the > Purchase Order. I click on it, an...

Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually, these queries return the correct amount of records in the tables. Which is 80 records in all tables involved. Then I built a query that collects data from the 6 queries and this query returns 75 records. What am I doing wrong? Thanks Don As a guess, your query is not correctly structured for the data you have. For instance, if one of the 6 tables does not have a matching record that could cause you to "lose" a record or two in the query if you have set up a join to that table and have n...

Logged-in user was not found in the Active Directory
Hey all, We just did a fresh install of CRM. Everything installed without errors, but when I try to access CRM via the server name I get a generic "Error - An error has occured. For more information please contact your system administrator." From the CRM server I can access is via localhost without any problems. The error that is logged in the event viewer is: Event Type: Error Event Source: Microsoft CRM Event Category: None Event ID: 2 Date: 10/13/2005 Time: 10:26:27 AM User: N/A Computer: SERVER Description: MSCRM Platform Error Report -----------------------------------...

Password protect cells
I have a sheet that people need to enter data into, but I have a problem with people deleting the formulas in other cells. Does anyone know if there is a way to password protect one or several cells without protecting the entire sheet? You can lock cells or unlock cells (format|cells|protection tab). After you lock/unlock cells, you can protect the worksheet (tools|protection|protect sheet) When the worksheet is protected, the user won't be able to changed the locked cells, but will be able to change the values in the unlocked cells. There are other things that are disabled when the...

automatically import data from a cell in one workbook to another workbook
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do arrange for workbook A to import the data from a cell in workbook B? In other words, what formula (address?) should I enter in a cell in workbook A so that it will import the data in a cell from workbook B? This would be just another formula; start with =+ and then just use the mouse/trackpad to click on the referenced cell (assuming you have both wordbooks open. Be careful of relative/absolute references. Thanks, Mungo <br><br>Cheers <br><br>Karshish 1) Make sure both workbooks ...

Address book clean up
I have a pretty big address book and I'd like to do some clean up. For one thing, I have a mixture of ways people are stored "last, first" and "first last" . Is there a global way to change that for everyone in the address book? Also I'd love to get rid of some strings in people's names like "(e-mail)", "(email)" and so forth. Is there a way to do that? (WinXP/ Outlook 2003) Regards. -- ------------ Michael Bierman Not sure what you mean by address book since Outlook does not have one. The address book is only a display of you...

Help with: A program is trying to access address book
I still can't get this dialog box to stop popping up. Currently Running Incredimail Outlook add-on and Mailfrontier v4.x. Any help would be greatly appreciated. -- **:::::>>:��:*�`����`*:��:<<:::::** .��~ �L�AzZ� W�A��L ~��. **:::::>>:��:*�`����`*:��:<<:::::** {��} WrOnG NuMbEr RoCoCcO, yOu .��~ �L�AzZ� W�A��L ~��. ..��~ �L�AzZ� W�A��L ~��. <nospam@here.com> wrote in message news:<MPG.1b02c40957db440f9896b4@news.newsgroups.ws>... > I still can't get this dialog box to stop popping up. > Currently Running Incredimail Outlook add-on an...

How do I get my text to wrap in a single cell in exel?
I can't get all my text in a single cell, it starts across in to the cell next to it instead of starting a new text row below? Thanks Hi DJz, Select your cell and go to Format-->Cell. On the Alignment tab, check the checkbox labelled Wrap Text at the bottom of that dialogue window. HTH, Katherine "DJz" <DJz@discussions.microsoft.com> wrote in message news:97B925D0-FF0C-4CC9-AB23-9CB2D9AC2597@microsoft.com... > I can't get all my text in a single cell, it starts across in to the cell > next to it instead of starting a new text row below? Thanks Hi Kathe...

Finding cell text corresponding to the MAXimum figure in a spreadsheet?
Please can anyone help me with this... :) I've made a spreadsheet with the following attributes: Cells A1 to A100 contain a figure from 1-100 (a 'desirability' rating), sorted so that the highest figures are at the top of the spreadsheet and descend to the lowest figure in cell A100. Cells B1 to B100 contain the title of a CD album. Cells C1 to C100 contain the price of a CD album. I would like to make a formula that searches for the row that produces the highest result given from multiplying the figure in column A with the corresponding figure in column C and returns the co...

can the Purging of deleted items be prevented
I have multiple users that like to purge the items that are in the exchage server from outlook without thinking whethere they will need them in the future. My question is, Is there a way to configure or prevent the purge from outlook? Donald Miami, FL Do you have deleted items retention enabled on the server (and is that what they are purging)? You may be able to use group policy to remove the Recover deleted item menu from outlook to prevent them from using it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual ...

NEED ALL FUNCTIONS BY SIZE (LIQUOR-WINE BUSINESS)
i am in the liquor business and everything we do is by SIZE. it would be great to have a data base field for Size. track sales by size discount formula by size( if x size and x quantity then % discount) etc. or is there a way to do this i am not aware of? ---------------- 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 "...