+ Reply to Thread
Results 1 to 11 of 11

Lookup value with multiple criteria to report back another value

  1. #1
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    212

    Lookup value with multiple criteria to report back another value

    Hi Forum,

    What I want to achieve is to be able to lookup a value in a cell containing text. The text for example is AxB.
    In this cell i want to extract the first character, so "A".
    I then want to use this value to lookup in a table with headers A,B,C,D,E
    I want to report back the value that corresponds to the same row that the formula is in.

    It's easiest to explain this with a worked example.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,970

    Re: Lookup value with multiple criteria to report back another value

    How about
    =INDEX($B3:$F3,MATCH(LEFT(G$2),$B$2:$F$2,0))*INDEX($B3:$F3,MATCH(RIGHT(G$2),$B$2:$F$2,0))

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup value with multiple criteria to report back another value

    I suppose you want this

    Try in G13 and drag down and forward the following formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    212

    Re: Lookup value with multiple criteria to report back another value

    Thanks so much for the speedy responses! Fluff your formula works great for the 1x1's but breaks at 1x1x1.
    Jose your formula works for all of the values so thanks very much for that!

    By the way will this formula work for 1x1x1x1 such as AxBxCxD and 1x1x1x1x1 such as AxBxCxDxE?

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,970

    Re: Lookup value with multiple criteria to report back another value

    Oops missed the 3 character requirement
    =INDEX($B3:$F3,MATCH(LEFT(G$2),$B$2:$F$2,0))*INDEX($B3:$F3,MATCH(RIGHT(G$2),$B$2:$F$2,0))*IF(LEN(G$2)<4,1,INDEX($B3:$F3,MATCH(MID(G$2,3,1),$B$2:$F$2,0)))

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup value with multiple criteria to report back another value

    By the way will this formula work for 1x1x1x1 such as AxBxCxD and 1x1x1x1x1 such as AxBxCxDxE?
    No, both formulas use only two or three products.

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

    Re: Lookup value with multiple criteria to report back another value

    Please try at G3
    upto 5 products and easily add more number in blue

    =PRODUCT(IFNA(LOOKUP(MID(G$2,{1,3,5,7,9},1),$B$2:$F$2,$B3:$F3),1))

    Confirm by Ctrl+Shift+Enter
    Attached Files Attached Files

  8. #8
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup value with multiple criteria to report back another value

    Hi Julian

    To perform to five products (AxBxCxDxE) you can use this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (Sorry but I couldn't get a shorter one)

  9. #9
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    212

    Re: Lookup value with multiple criteria to report back another value

    Thanks Bo_Ry and Jose augusto! Both formulas work brilliantly!
    This is now solved!

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,968

    Re: Lookup value with multiple criteria to report back another value

    Alternative option with non array formula (simple Enter to confirm typing)

    G3:

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    212

    Re: Lookup value with multiple criteria to report back another value

    Quote Originally Posted by bebo021999 View Post
    Alternative option with non array formula (simple Enter to confirm typing)

    G3:

    Please Login or Register  to view this content.
    That formula does not seem to work. Seems to result in 0 for all cells.

+ 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. lookup cell and report back the best matching result
    By jaamba123 in forum Excel General
    Replies: 13
    Last Post: 12-24-2016, 06:05 PM
  2. multiple criteria lookup to bring back MAX date
    By AlexDNI167 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-05-2016, 10:26 AM
  3. [SOLVED] URGENT - Match Criteria and bring back multiple rows
    By Keelin in forum Excel General
    Replies: 2
    Last Post: 12-10-2014, 12:06 AM
  4. V-Lookup giving back only first match when their are multiple listings
    By astan92 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2014, 02:25 PM
  5. Replies: 4
    Last Post: 02-18-2014, 08:37 PM
  6. lookup/report back a value and column header
    By tooleyr in forum Excel General
    Replies: 2
    Last Post: 01-14-2011, 04:36 PM
  7. Replies: 1
    Last Post: 10-22-2009, 05:26 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