+ Reply to Thread
Results 1 to 11 of 11

Vlookup where the reference is number formatted in one sheet and text formatted in the sec

  1. #1
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Vlookup where the reference is number formatted in one sheet and text formatted in the sec

    Hi Everyone,
    My question is very simple, but almost got me in big trouble. it figures you cannot rely totally on excel, and as much as a formula looks simple and effective it might mislead in cases the data just looks the same but formatted differently.

    So I have 2 files , one for August, second for July (in my example I have put them in 2 tabs for convenience) . The real file consists of 1000 lines each.
    so - I was trying to lookup values in Aug. that already existed in July in order to remove them. sound simple......


    I used this formula in F2, and wherever the answer returned "1" - it means there is a match to the other tab.
    =LARGE((A2=Jul.!A:A)*(Aug.!E2=Jul.!E:E),1)

    Everything went well for the majority, but it missed to find a match for lines 9-10, where they do appear on July, but formatted as text.

    Does anyone have any advice how to bypass this without having to change the data or review one by one to find such discrepancies?
    The result was that the excel didn't recognize that these invoices were already paid last month, and we almost submitted them for a duplicate payment.

    Thanks everyone for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Two possible ways:

    1) An extra manual step to select Aug.!E:E then data > TextToColumns and specify "Text"

    or

    2) The following mod to your formula gives the result you expect, I think:
    =LARGE((A4=Jul.!A:A)*(TEXT(Aug.!E4, "0")=TEXT(Jul.!E:E, "0")),1)
    (array formula still)
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Thank you GeoffW283, so I will need to do text to column to both files, because there might be cases where it could be the other way (former file is a number, and the new is a text. right?

    Thank you very much.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,482

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    With a pivot table you see the differance.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    That's nice Oeldere, very visual and easy to capture . thank you

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,727

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Belinda,
    I took a different approach. I loaded each table to Power Query and saved(closed and loaded to a connection)
    I then joined (merged) the two tables based upon a full join on the legal name and document number.

    You can review this in the attached file. Results look like the following.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    1
    Legal Name Period Country Currency Document Number August.Legal Name August.Document Number
    2
    MEDIA LLC Jun 2020 US USD URMAY20 MEDIA LLC URMAY20
    3
    4M Jun 2020 JP JPY
    202004
    4M
    202004
    4
    4M Jun 2020 JP JPY
    202006
    4M
    202006
    5
    A&N Jun 2020 AU USD SIN463902 A&N SIN463902
    6
    PVT LTD Jun 2020 IN USD
    1.95E+11
    PVT LTD
    1.95E+11
    7
    OVATION INC. Jun 2020 JP JPY 202005 OVATION INC. 202005
    8
    PR LIMITED Jun 2020 GB GBP ATUPR0119 PR LIMITED ATUPR0119
    9
    TIVA, INC.
    3137
    10
    TIVA, INC.
    3140
    11
    RITHM INC Jun 2020 JP JPY 202005 RITHM INC 202005
    12
    TIVA, INC.
    3153
    13
    ALL Jun 2020 JP JPY
    202004
    ALL
    202004
    14
    ALL Jun 2020 JP JPY 202005 ALL 202005
    15
    ONLINE UK LIMITED Jun 2020 GB USD 420014299 ONLINE UK LIMITED 420014299
    16
    TIVA, INC. Jun 2020 US USD 3137
    17
    TIVA, INC. Jun 2020 US USD 3140
    Sheet: Sheet1
    Attached Files Attached Files
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    My solution is trying to convert all into text:

    =SUMPRODUCT(($K$2:$K$14=A2)*($O$2:$O$14&""=E2&""))

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Thanks Alan, but the files might not be structured the same. The real file has 20-30 columns, and I dont want to work hard to make them symmetric.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Hi Bebo,
    The formula doesn't return for me the requested result. It brings zero match for lines where a match should be found.......can you please look at the attached and amend where needed?

    Thanks.
    Attached Files Attached Files

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

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Adapt to your sample:

    =SUMPRODUCT((Jul.!$A$1:$A$15=A2)*(Jul.!$E$1:$E$15&""=E2&""))

  11. #11
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,715

    Re: Vlookup where the reference is number formatted in one sheet and text formatted in the

    Many thanks Bebo.

+ 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] =vlookup in formatted text
    By BRISBANEBOB in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2020, 02:24 AM
  2. [SOLVED] incorrectly formatted reference number
    By bassinator in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-27-2019, 05:35 AM
  3. [SOLVED] Vlookup if text is formatted in a certain way
    By alesha711 in forum Excel General
    Replies: 8
    Last Post: 04-16-2019, 12:16 PM
  4. Is a cell containing a number really formatted as Text?
    By spshipley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-13-2016, 10:17 AM
  5. Text when formatted as number will not Sum
    By Laslo1 in forum Excel General
    Replies: 2
    Last Post: 07-23-2012, 10:35 AM
  6. Converting 'General' formatted cells to Text formatted cell using.
    By Zahid Khan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2005, 04:06 PM
  7. Replies: 1
    Last Post: 02-07-2005, 02:06 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