+ Reply to Thread
Results 1 to 8 of 8

Vlookup help

  1. 08-01-2016, 05:21 AM #1
    DarrenCl
    DarrenCl is offline
    Registered User
    Join Date
    04-02-2015
    Location
    Southampton, England
    MS-Off Ver
    Excel 365
    Posts
    41

    Vlookup help

    Hi,

    I need help with a vlookup formula, basically I have a list of companies in column B and I have another list of columns in cell D with an ID number for that company in cell C.

    I need to run a formula in cell A that checks if the company in cell B exists in cell range D2:D100000 and then show the ID number of that company from cell C as the result.

    Any help would be great, I've become a bit stuck!

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 08-01-2016, 05:31 AM #2
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Vlookup help

    it sounds like =INDEX(C:C,MATCH(B1,D:D,0)) might work for you. You can't use a vlookup because you want to go backward.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  3. 08-01-2016, 05:31 AM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Vlookup help

    Try

    =IFERROR(INDEX($C$2:$C$100000,MATCH($B2,$D$2:$D$100000,0)),"")
    Reply With QuoteRegister To Reply
  4. 08-01-2016, 05:37 AM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup help

    Quote Originally Posted by Sambo kid View Post
    it sounds like =INDEX(C:C,MATCH(B1,D:D,0)) might work for you. You can't use a vlookup because you want to go backward.
    INDEX is the best option.

    =VLOOKUP(B1,CHOOSE({1,2},D1:D10,C1:C10),2,0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 08-01-2016, 05:40 AM #5
    DarrenCl
    DarrenCl is offline
    Registered User
    Join Date
    04-02-2015
    Location
    Southampton, England
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Vlookup help

    Awesome, you're all geniuses, all of them did what I wanted
    Reply With QuoteRegister To Reply
  6. 08-01-2016, 05:42 AM #6
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Vlookup help

    =VLOOKUP(B1,CHOOSE({1,2},D:D,C:C),2,0)
    Good one Tony! I've actually used that one in the past and did recommend it once and was told by a forum guru (or moderator - don't recall which) not to use it because it is not standard and too difficult to explain. But you are right, it will work for going backward.
    Reply With QuoteRegister To Reply
  7. 08-01-2016, 08:42 AM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup help

    I'm reluctant to say "you can't do this or that" because someone will usually prove me wrong!
    Reply With QuoteRegister To Reply
  8. 08-01-2016, 08:42 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup help

    You're welcome. We appreciate the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coresspond
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 34
    Last Post: 07-07-2015, 09:32 AM
  2. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  3. Find max value in range then vlookup text in correspond col header & vlookup to table
    By jcskwells in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. how to bring all vlookup returns even with duplicate vlookup search keys
    By NYC4LIFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 04:53 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Howto: Vlookup looping (for each...next?). Advise needed about Vlookup loop technique
    By Wino in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2009, 07:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1