Vlookup with Multiple Rows to match with one row

I am trying to match a one record to a list with multiple like records and 
only retrieve from the first record:

Record 1 Jim Smith

look up to Record 2

Record 2 Jim Smith Nice Guy
Record 2 Jim Smith Not so Nice Guy

 In otherwords when I do my vlookup 
VLOOKUP(B12,'Overall Transformations '!A:Y,17,0) it sees the two records in 
record 2 and brings back N/A#.

How do I tell it to bring back the first instance that it sees with Jim 
Smith Nice Guy?

5/11/2010 9:20:01 PM
excel.misc 78881 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 30

Me thinks something else is causing the #N/A.

If you have two  Jim Smith's in the lookup array, it should return the first 
one only and not "see" the second one.

(I assume the N/A# in your post is a typo)

Might not really have a match...?  Spaces or something like that.


"Motaad" <Motaad@discussions.microsoft.com> wrote in message 
>I am trying to match a one record to a list with multiple like records and
> only retrieve from the first record:
> Record 1 Jim Smith
> look up to Record 2
> Record 2 Jim Smith Nice Guy
> Record 2 Jim Smith Not so Nice Guy
> In otherwords when I do my vlookup
> VLOOKUP(B12,'Overall Transformations '!A:Y,17,0) it sees the two records 
> in
> record 2 and brings back N/A#.
> How do I tell it to bring back the first instance that it sees with Jim
> Smith Nice Guy?
> -- 
> Motaad 

5/11/2010 10:31:22 PM

Similar Artilces:

My links with in an email that some one sends to me will not open
I have Outlook 2007 and up until a few months ago it was working fine. Now when I receive an email from a someone or a company that contains a link to the web I can't click on it to open it. It gives me an error message of "this operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator." I did find a work around temporarily by going to other actions and selecting view in brower. But that is a real pain. It worked fine before and I didn't change any settings. Please help. Thank you in advance. See if t...

multiple IF
I have a spreadsheet as follows Col B can be either F or N Col J Numbers From 5 TO 36 Col W Numbers from 1 to 100 I want to create a new column based on some complicated frormulae. If col b is F and col j is less than 5.9, the formula is 2.193*((.99^w1)*(w1^.584)) where w1 is the number in that cell. If col b is F and col j is between 5.9 and 6.9 the formula is 2.424*((.99^w1)*(w1^.668)) where w1 is the number in that cell If col b is F and col j is between 6.9 and 7.5, the formula is 2.79*((.99^w1)*(w1^.612))where w1 is the number in that cell If col b is F and col j is betwe...

Purchased the product, used one of the product keys, I want to sell it on Ebay will the buy be able to use the product keys.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Also will it allow me to run office if they use the other 2 keys? In order to legally transfer the software to another user you must remove it from any system you have it on. Additionally, all keys in the package are covered by the same license, so no, you can't legitimately "keep" any of them - unless you're a part of that buyer's 'household' & tehy agree to you doing so. -- HTH |:>) Bob Jones Office:Mac MVP <googlepepsi@officeformac.com> wrote in message news:59b72405...

how do I print a one page annual calendar in outlook?
Outlook provides no built-in way to see an entire year's worth of appointments. One solution is to use the Outlook 2000+ Year View control from http://www.planetsoftware.com.au/products/yearview.aspx. This ActiveX control, designed for use in a web page, shows your all-day appointments for any given year. You can create new appointments right in the web page, color-code them to match your Outlook categories, and print out year-planner view on a single page. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and...

Vlookup Rounding
I am "trying" to build a spreadsheet at work illustrating a gain/share model that is triggered off of revenue & volume in work. Given that information, here is one part of the spreadsheet I am having complications with. For a plug number I have my current revenue set at 13,000,000. If i was to receieve an additional 200,000 in work next year the following year (13,200,000), that would make a revenue increase of 1.5% (VALUE #) from the previous year. Based off of the the percentage of increase, I would like to offer the customer a "discount" capped at a certain perce...

Importing a text file w/ more than 65,536 rows
I'm trying to download a text file which exceeds the number of rows on an Excel worksheet. The help menus for Excel 5.0, 7.0, and '97 specify a way to use the Text Import Wizard to import the file to multiple worksheets. However, I have Excel 2000, and cannot locate any instructions about how to download to multiple worksheets. Anyone have any suggestions? I'd use my favorite text editor and split the text file into smaller pieces. Then import them separately. Or you could use a macro that imports each line: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596 ...

Average with multiple conditions
I'm trying to calcluate the average where multiple conditions have to be met. I've tried a couple of different formulas based on other postings, but I continue to get "0". I'm trying to calculate the average of months open (Column F) if the following conditions apply: Date closed is >= A1 Date closed <=B1 Bucket = "12 Month" Column D Column E Column F Date Closed Bucket Months Open 1/5/2006 12 Month 11.2 1/1/2004 24 Month 16 2/1/2006 48 Month 52 2/5/2006 12...

How do I send a fax to multiple receipients through outlook?
I am trying to send a letter to multiple different sites using outlook and our fax machine, not using efax services. Outlook can't talk to a standalone fax machine. You could perform a mail = merge in Word and print out different personalized letters and then fax = them manually. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Jones" <Jones@d...

Deleting Duplicate Rows #3
Hello, I work in Asset Management and frequently use excel spreadsheets for record keeping. I'm trying to filter down a list of 500 computers by eliminating duplicates. Is there a quick easy way to delete all duplicated fields? Thank you, SP -- pettes01 ------------------------------------------------------------------------ pettes01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28655 View this thread: http://www.excelforum.com/showthread.php?threadid=483172 Many methods. Start here: http://www.officearticles.com/excel/dealing_with_duplicate_reco...

Multiple SOP Windows
Is there a setting in version 7.5 software setup that allows one user to have more than one SOP window/document open at one time. If not this is simply very poor design. Without this ability it is very cumbersome to handle say periodic customer inquiries and at the same time work at another task such as new order entry. One should simply be able to flip from one window/document to another. -- Best Regards George You can launch another GP session; although, that will use another user license. For inquiries, could a person not use the Sales Document Inquiry to look up information? ...

COUNTIF using multiple conditions
I have two columns of data. One of the columns lists sources by which customers heard of our company. Another contains the status of the appointment. Ex: COLUMN A COLUMN B Yellow Pages LOST Radio SOLD Truck Signs SOLD Yellow Pages LOST Yeloow Pages SOLD etc...... I want to take a count of how many times an appointment from a certain source is sold. In other words, I need it to meet both criteria. I've tried using the COUNTIF function but can't seem to work it out. I've looked through other postings and tried =SUMPRODUCT((Sheet1!A:A...

Different price lists on one quote in CRM 3.0
Is there any way to customize CRM to allow you to select a price list for every each product line in quote, order and invoice? We need this because we have different products from different suppliers which we want to put together for a solutions quotes. Hi TTL This can be done, you must choose all products from one price list, then save it, then you can change the price list and choose other products, save it and choose another. You must save in between changing lists otherwise it wont work. HTH Ben This will seem to work at first. BUT when you click the recalculate button or change so...

Determine the Empty row and/or column
I want to determine if a particular row or column is empty using VBA?? I can determine the last used row in the given worksheet. But this is differenet from determining the last used row. As other row below the empty row may have data in it. Hi, Try these 2 which return TRUE if the row or column is empty RowEmpty = WorksheetFunction.CountA(Rows(1)) = 0 ColumnEmpty = WorksheetFunction.CountA(Columns(1)) = 0 Generally, I find this webpage a great reference for last row etc http://www.mvps.org/dmcritchie/excel/lastcell.htm -- Mike When competing hypotheses are otherwise ...

I m preparing invoice for my company which have multiple sheets like Customer List and Pipe Size detail with rate of specific pipe. I want to locate rate of specific pipe in Invoice. For this purpose I m using vlookup function but when in that position second row is blank or same then it returns #N/A. I just want only want that it should be returned blank when row of Pipe detail remain blank. My worksheet have this type of data. A B C D E F G Description No. of Pipes (Qty in Mtr) Total Meter Rate Amount 1 063mm x 4Kg TKT 25 6 150 17.42 2613.00 2 075mm x 4Kg TRN 25 6 150 21.76 3264.00 ...

Stop adding rows!
This must be easy but I can't figure it out. How would I tell Excel to not add rows upon vertical scrolling? I just want to limit the length to 70 rows. Thanks. -- Gnarlie http://Gnarlodious.com/ Gnarlie You cannot delete unused rows columns but, if you want to not show the rows/columns, then you could select them and go to Format>Row (Or column)>Hide. If you want to restrict movement then... You can't restrict it in the standard user interface, but you can, either through code or through the VBE. In Code, in a standard module enter (this restricts the movement to row...

Summing the results of the VLOOKUP command
I am using the vlookup command several times to return values found in another worksheet. I would like to SUM the results of the vlookup command but since the values are not constants the SUM funtion is returning an error. How can I sum the results of the VLOOKUP command? You should be able to sum (numeric) results of formulas, no matter what formulas. What are the results of your lookups? What does your SUM formula look like? What result did you expect? What did you get? -- Kind Regards, Niek Otten Microsoft MVP - Excel "James" <james_barrum@adp.com> wrote in message ne...

One instance and set to foreground process
Hi, I have what I suspect is a very common problem, I need to make sure there is only one instance of my program running. The second instance must set the first instance to foreground (we assume the user has minimized the window and forgot he had one running), then the second instance must exit quickly without popping up any windows. I can get a process id of the first instance by using EnumProcesses and match the *.exe filename, but how do I send this process id to the foreground? It seems the term "foreground process" is frequently used in microsoft documentation, but no mention...

sync. multiple copies of outlook 2002
How can I sync. email, tasks etc. between multiple copies of outlook 2002 I.E. between laptop and desktop? Thanks Hello Chris, Have a look at www.slipstick.com/outlook/sync.htm or at my signature. Thomas Wetzel Outlook synchronization for mobile users www.synchpst.com ...

lookup from one column return value from another?
I want to check the value of one column (A) and then depending on the outcome add the value in the same row but column B to a total. Can I do this in a single cell formula or do I have to have an extra column that does the logic test and then a cell to do the totalling? Hi Maria not sure of exactly what you want but does =IF(A1="Fred",B1,0) give you what you want? if not could you type out an example of your data and what you want to see (please don't attach a workbook just type it out) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working...

Multiple Lookup
I'm making a lookup for software licensing. I have Manufacturer, Product and Version. I want to first choose Manufacturer (say Microsoft), then I want to choose the Product (say CRM) and then the Version (say 3.0). I have all three lookups working fine except that if I choose Version I get every possible version I have whether it applies to the product CRM and Manufacturer Microsoft or not. Without buying a third party solution how can I achieve this. ...

How to use COUNT using multiple ranges
I need the count of information based on criteria from differen columns. Example of columns: A B C D 0_F JJ 1 II 0_F JD 2 III 0_R JR 1 II 0_F JC 2 II Blank JP 1 III 0_F JK 1 III What is the count IF (Col A)=0_F and IF (Col D)=II I don't know how to combine the information to come up with the resul of 2 Thanks for any help -- Message posted from http://www.ExcelForum.com On 5/9/04 7:38 AM, in article LIZZIE.15zlef@excelforum-nos...

how can i make business cards multiple in a page?
pls. pls. pls. any answers would help!!!!!!!!!!!!!! I suspect you are printing a single card in the middle of an 8�"x11". In Publisher 2000 (similar in other versions; let's hope) when you select File, Print, then the Page options button, Print multiple copies per sheet, custom options button to check that your gaps and margins are what you want. Then OK Ok printer runs NOW Save the file to retain all the settings you just made. Next time you print cards you will only need to select File, Print. -- Don Vancouver, USA "sha" <sha@discussions.microsoft.com...

One or Multiple Mailbox Stores
This is a multi-part message in MIME format. ------=_NextPart_000_0040_01C57730.25113F30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have multiple Exchange 2003 servers. Each Exchange server has two = storage groups. Each storage group resides on a different RAID array = with logs and databases on separate partitions. I have only one Mailbox = Store created in each storage group. My largest Mailbox Store currently = holds 350 users. =20 Question: What are the advantages of creating multiple Mailbox Stores = in a storage group...

vlookup #23
Hello all: I hope you can help with the following: I have to lookup a number in worksheet A within a range, "Range", i the number is not available in the range, I want a "" in my cell. I the number is available it should lookup the 18th cell. In the 18t cell, I could have a date in there OR a words such as confirmed, route e.t.c. If it is a date, it should provide me the date in my cell, i it is anything other than confirmed, it should put a blank, if it i confirmed, it should take cell m2+3 workdays. I have the followin formula but I all blanks, something is not righ...

vlookup in a chart
I am trying to create a chart which looks up a value and then depending on this value uses a certain set of data to draw the chart. I know it can be done, as I have seen it, but I am not sure what I need to do Can you bee a little more specific? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Lottie" <Lottie@discussions.microsoft.com> wrote in message news:6A5802C0-EDC3-429A-BDEC-DFB73821461F@microsoft.com... >I am trying to create a chart which looks up a value and then depending on > this value uses a certain set of ...