+ Reply to Thread
Results 1 to 3 of 3

Xlookup to replace VLookup in complex problem

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,056

    Xlookup to replace VLookup in complex problem

    I have an old worksheet that I think I originally got from Chandoo, showing how to lookup mixed up names and return valid results. For instance, if your lookup value is "Johnson,John " and the table you're looking in has John in one column and Johnson in the other. I'm trying to figure out how to do the same thing with XLookup, and so far failing miserably. See the attached workbook. The formula in C19 I've already solved with Xlookup, but the ones in B12:B15 are the ones that are stumping me.

    Any help is appreciated.
    Attached Files Attached Files
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Xlookup to replace VLookup in complex problem

    Please try at
    B12
    =XLOOKUP(A12,A$4:A$7&" "&B$4:B$7,C$4:C$7)

    B13
    =XLOOKUP(A13,B$4:B$7&", "&A$4:A$7,C$4:C$7)

    B14
    =XLOOKUP(1,(A14=A4:A7)*(A15=B4:B7),C4:C7)
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,056

    Re: Xlookup to replace VLookup in complex problem

    Bp_Ry, you're a genius! Thanks so much for the solutions. I'm tryin to master Xlookup, so I appreciate the techniques you've used. I'll learn from them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Xlookup vs vlookup
    By Mr_Phil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2021, 03:19 PM
  2. [SOLVED] Help with complex (to me) XLOOKUP function to create interactive table
    By frleisc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2020, 04:24 PM
  3. Help with complex (to me) XLOOKUP function to create interactive table
    By frleisc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2020, 01:18 PM
  4. SUMPRODUCT or complex VLOOKUP? Inventory management problem
    By Rusty315 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-13-2014, 01:14 AM
  5. [SOLVED] Help coding a complex vlookup problem
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-17-2014, 12:47 PM
  6. [Solved] VBA Vlookup and complex Formula problem
    By Bansheete in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2013, 08:23 AM
  7. Replies: 2
    Last Post: 03-06-2006, 12:40 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1