+ Reply to Thread
Results 1 to 24 of 24

Look up formula from table with uneven format

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Look up formula from table with uneven format

    Hi Friends,

    I need help in building a lookup formula (index match)

    issue here is the look up values are different in formats in source table and output table

    see attached please. Without changing formats in either source table or output table, can we get a straight formula that can automatically pick up uneven formats

    thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    Re: Look up formula from table with uneven format

    Please update your profile with your current version of Excel.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    Re: Look up formula from table with uneven format

    This will cover all but the last row:

    =IFERROR(IFERROR(IFNA(VLOOKUP(VALUE(A2),$A$9:$B$12,2,0),VLOOKUP(A2&"*",$A$9:$B$12,2,0)),VLOOKUP(VALUE(A2)&"*",$A$9:$B$12,2,0)),VLOOKUP(A2,$A$9:$B$12,2,0))

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    Re: Look up formula from table with uneven format

    This covers everything:

    =IFERROR(IFERROR(IFERROR(IFNA(VLOOKUP(VALUE(A2),$A$9:$B$12,2,0),VLOOKUP(A2&"*",$A$9:$B$12,2,0)),VLOOKUP(VALUE(A2)&"*",$A$9:$B$12,2,0)),VLOOKUP(A2,$A$9:$B$12,2,0)),VLOOKUP("*"&A2,$A$9:$B$12,2,0))

    HOWEVER, it's a workaround that does not tackle the fundamental underlying issue of mismatched data - your best long-term course of action would be to tackle the data at source.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,896

    Re: Look up formula from table with uneven format

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  6. #6
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    Thanks heaps Ali GW. That was really helpful

    Apologies Alansidman.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    Re: Look up formula from table with uneven format

    The apology is not enough - please provide the link requested.

    Please also review post #2 and do as requested there.

    You cannot ignore moderation requests.

  8. #8
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    Hi AliG, Can I ask how would the above formula shape up if I needed to reference a second cell

    If formats were consistent I would have used below
    I would normally use INDEX(Base!$AN$2:$AN$399,MATCH($I11&$P11,Base!$D$2:$D$399&Base!$C$2:$C$399,0))

  9. #9
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    Last edited by AliGW; 07-02-2020 at 04:33 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    Re: Look up formula from table with uneven format

    Thanks for the link.

    Can I ask how would the above formula shape up if I needed to reference a second cell
    I'm not even going to consider going there. Please re-read the third line of post #4: you are making your life far too difficult by not addressing the elephant in the room. I suggest you sort out the cause of your issues rather than trying to work round it and them. Good luck!

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,694

    Re: Look up formula from table with uneven format

    Another solution that works too:

    Please Login or Register  to view this content.
    Last edited by bebo021999; 07-02-2020 at 05:45 AM. Reason: add *

  12. #12
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    thank you bebo that was really helpful. could you also advise how the formaula would shape up if I need to reference to a second variable/criteria

    in my initial example we are looking up A2 in range A9:A12

    I need to reference to one more variable- say B2 in range B9:B12

    thanks heaps

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,694

    Re: Look up formula from table with uneven format

    Upload new worksheet with new variable in B2 and B9:B12

  14. #14
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    HI Bebo, Please find attached

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: Look up formula from table with uneven format

    Please try the following modification of bebo's formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  16. #16
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    Thanks JetemC. This worked. I will go through formula and try to get my head around it

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: Look up formula from table with uneven format

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. Using the Evaluate Formula feature may help in understanding how the formula works. I hope that you have a blessed day.

  18. #18
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    HI Jtmc
    I noticed that when I remove the alphabet next to the number in lookup table, answer references only to the number and ignore the alphabet and returns same answer
    that is if we need to look up answer to 03004 and the look up table has 03004(B)...answer returned is for 03004....not a great concern for now...but wondering how the formula can be adjusted
    Please see attached

  19. #19
    Registered User
    Join Date
    07-06-2020
    Location
    Brasil
    MS-Off Ver
    2013
    Posts
    2

    Re: Look up formula from table with uneven format

    Hi, sorry. My excel is portuguese and I have to sleep now, but this resolve your issue.

    =SEERRO(PROCV(VALOR(A2);$A$8:$B$12;2;0);PROCV(A2;$A$8:$B$12;2;0))

    See you, it is my firt day here

  20. #20
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    thank...didn't seem to work as it references only one variable when I need to look up two

  21. #21
    Registered User
    Join Date
    07-06-2020
    Location
    Brasil
    MS-Off Ver
    2013
    Posts
    2

    Re: Look up formula from table with uneven format

    Did you drag down my formula?

    'Cause I can not understand what more you need, if you could explain more
    Probably I can not understand 'cause my bad english but I am praticing every day and I wanna help you

    Plese try to explain again to me

    See you

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: Look up formula from table with uneven format

    The following formula seems to display the correct values:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For future reference please provide the correct values for instances where the formula produces an erroneous result so that we can verify the results of our corrected formulas/code.
    Let us know if you have any questions.

  23. #23
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Look up formula from table with uneven format

    thank you Jetemc.

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: Look up formula from table with uneven format

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Interest payment formula for uneven cash flows
    By Jairam Jana in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-24-2019, 11:06 AM
  2. Replies: 15
    Last Post: 02-12-2015, 03:19 AM
  3. [SOLVED] Formula required to split uneven data into different columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-14-2015, 04:25 AM
  4. [SOLVED] Formula to skip uneven numbers
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-22-2014, 08:36 AM
  5. To Select Cells and format them, when there are uneven number of rows and columns
    By sankavi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2013, 02:37 AM
  6. Running formula with uneven number of rows
    By jacquiG in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2012, 04:56 AM
  7. NPV Formula Using Uneven, Monthly Cash Flows
    By DanoT in forum Excel General
    Replies: 1
    Last Post: 02-20-2012, 05:02 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