Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup (Excel 2000)

    Can you help me? I know the Vlookup function very well. However, instead of searching for one value in A1, I need the Lookup function to search values in A1 and B1 and C1, and then return the answer in D1. I keep the Lookup range over in $O$2:$U$45. The third column houses the data I need. Thank you.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    I'm not sure I understand your question. What do you mean by "search values in A1 and B1 and C1"? If A1 contains "John", B1 contains "Q.", and C1 contains "Smith", then what are you searching for in O2:O45? "John Q. Smith"? "JohnQ.Smith"? Something else?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup (Excel 2000)

    I second Legare's request for more info.

    Are you looking to find a value that could be in 3 columns?

    Does this <post#=356634>post 356634</post#> have a setup and solution similar to what you are looking for?

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    Thank you. Your example is my exact issue. In A1 is the first name, B1 is middle initial, and C1 is the last name. I need all three variables to be true, then the response in D1 would be the correct employee number. I hope this is more clear.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    Since you didn't say which of my examples is your exact issue, I will assume it is the first. Try something like this:

    <pre>=VLOOKUP(A1&" "&B1&" "&C1,$O$2:$U$45,3,False)
    </pre>


    This assumes that column O contains the full name.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    The first name is matched with column O in the lookup range, middle name is Column P, and the last name is Q. What I want is the answer of the employee number which is in column S. I don't need to concatenate, what I need is to have O, P, and Q to be true and then return the employee number. Thank you.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup (Excel 2000)

    Try this ARRAY formula(confirm with ctrl-shift-enter). It assumes that there is only 1 person with that FirstName, MI, and LastName
    =SUM(IF((O1:O45=FirstName)*(P1:P45=MI)*(Q1:Q45=Las tName),S1:S45))

    If there is a possibility of more than one, you can use this to get the ID of the first one the one with the lowest row # (Again confirm with ctrl-shift-enter)
    =INDEX(S:S,MIN(IF((O1:O45=FirstName)*(P1:P45=MI)*( Q1:Q45=LastName),ROW(S1:S45))))

    Also if you would just create a column in your range that concatenates the FIrst MI & Lastname, you could just use a the Vlookup directly as suggested by Legare.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    I keep trying your solution, but I keep getting an error message, #NAME. I will work more tomorrow. Thank you for your assistance. I think you are brilliant.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    Try this modification of Steve's array formula:

    <pre>=INDEX(S:S,MIN(IF(($O$1:$O$45=A1)*($P$1:$P$45 =B1)*($Q$1:$Q$45=C1),ROW(S1:S45))))
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (Excel 2000)

    It worked! You are AWESOME. You are AWESOME. You are AWESOME. Thank you.

Posting Permissions

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