+ Reply to Thread
Results 1 to 35 of 35

When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coresspond

  1. #1
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coresspond

    Sir,

    1 lookup range are covered in single column(A2:A513) the sheet Name is Req Format.
    2 lookup range are covered double column (A2:A183) and (E2:E183) the sheet Name is Data
    3 Am apply lookup formulas two times from to various range that the cell is column F&G the Sheet Name is Req Format.

    Formula is

    Column F:

    {VLOOKUP($A3&B3&C3,CHOOSE({1,2,3},Data!$A$2:$A$183&Data!$B$2:$B$183&Data!$C$2:$C$183,Data!$D$2:$D$183),2,0)}

    Column G:

    {VLOOKUP($A3&B3&C3,CHOOSE({1,2,3},Data!$E$2:$E$183&Data!$B$2:$B$183&Data!$C$2:$C$183,Data!$D$2:$D$183),2,0)}

    4 after apply the formulas both of two column value return is wrongly(first cells value picking)
    5 How do combined two ranges

    My querry is

    When vlookup ranges is multiple: How do vlookup Value finding Occurrence against corresponding to the reference.

    file attached.

    please help me.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Please help me

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    I don't what happened to your original post which I (thought) I Had relied to.

    First VLOOKUP only returns the first value it finds.

    Second what are you trying to achieve by the 2 VLOOKUPs which look at the same D column using the 2 vales of "MAT. DOC"?

  4. #4
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Am apply the lookup formula 2 cells that columns is F and G.the value return is wrongly.value picked first cells only.I have highlighted in red colour.I need look up occurrence value.each duplication entry.please help me John topley.

  5. #5
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    I want combined lookup value (column F and G) against corresponding to the reference

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    See attached:
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,995

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Im not sure how you got some of your values (F10, for instance), but try this regular formula, copied down...
    =IFERROR(INDEX(Data!D:D,MATCH('Req Format'!A3&'Req Format'!B3,INDEX(Data!A:A&Data!B:B,0),0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Thank you so much for your response sir Mr.JohnTopley and FDibbins

    sorry for inconvenience.

    i have attached my requirement.lot of duplication are there in each entry.

    please help me.
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    I am even more confused.

    Unless you can explain in business terms what you require between the Cancellation invoices and the Original invoices I cannot proceed.

    Do you want a list of Original Invoices with Cancellation invoices removed? Then do you want to total the amount by invoice number?

    And how does this data relate your original file posted.

    Going out now.

  10. #10
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Sorry sir.

    I have just change the name Original&Cancellation Invoice No instead of material doc no.

    as requested to you i have change the my format.pl help me.

    file attached.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    You have not answered my questions about EXACTLY what you want calculated and how. I simply do not understand why some items are shaded red whereas others are blue. The "Req Format" tells me nothing.

    The following appears in A,B,B once but in but In G ,H,I 3 times: highlighted in blue

    4903717231 26218264 2

    This appears once in both sets but is highlighted in red - why?

    4903720140 26218264 2 1,810.00

    And there are other similar cases to the above.

    Why are these red?

    4903742391 26249929 1 1,510.00
    4903742391 26249929 1 2,850.00
    4903742391 26249929 1 3,850.00
    4903742391 26249929 1 2,520.00
    4903742391 9000-504 100 915.00

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    in adition to #11

    Please also add manualy the expected result in your file.

    Mark the related cells e.g. with the same color.
    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.

  13. #13
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Am really struggling with explanation about of thread and i feel very guilty and say sorry for the degenerative of your time.

    thank you so much for your response.

    i have highlighted both of color Red and Blue are duplication that the column is column A,B&C and G,H&I.

    JohnTopley formulas based on am apply the formula in column k2 also i have mentioned the column Name of JohnTopley.

    ={VLOOKUP($G2&H2&I2,CHOOSE({1,2,3},$A$2:$A$29&$B$2:$B$29&$C$2:$C$29,$D$2:$D$29),2,0)}

    The formula picking the return value is perfect without duplication columns in lookup range.

    with duplication arrive in lookup range in column value return is wrongly (first lookup range value only picking but my required format entirely different).

    please refer the column J&K and see the difference between My Req format and Formula appearance.

    My query is how do vlookup value get concurrence against multiple corresponding to the reference also i have mentioned and what i wantto achieve this in column J.

    please help me. file attached.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Please help me

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    please explain why row 38 is 2520 (instead of 0)

    4903724826 26249929 1 2520

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    See the attached: sheet "Data (2)"

    You will see I have used 2 helper columns to try and identify the "duplicates". This also requires the data be sorted: first by A,B,C and D and then bu G,H and I (using Sort ==> Custom)
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    With reference to the row no 7 2520.4903724826 26249929 1 2520.column a,b,c,d my raw database sir.please check

  18. #18
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Thank you so much sir for your reply with out additional column if not possible for this

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Sorry but I have now finished with this thread.

    I don't understand your query in your last posting and I am not prepared to spend any more time resolving queries.

    As I suggested earlier you should consider changing/adding to your data to simplify this process.

  20. #20
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Thank you so much and extremely for the inconvenience.as requested to you please give me one day i will clearly explained to you sir.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    See Attached "Data (3)".

    I added an extra column with an "X" to designate the data that was originally in columns A-D. I then added this data to that in columns G-I, and sorted. (deleting the original A-D columns).

    What are the expected results and WHY?? You should be able work out a solution.

    If approach this provides the results you require then I suggest you add a column to your data and "X" or whatever code is meaningful

    For example, if an invoice is cancelled use "C", if active use "A".

    Keep it simple!
    Attached Files Attached Files

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    See the green cells in the attached file.

    Although the file seems to be corrupted.

    R33, R35, R38 don't give the expected result in the countif formula (see the blue cells).

    So test it in your original file.

    Please reply.

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    @oeldere: Unable tom download file ... length of file name?

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    @John Topley,

    see attachement.

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Even worse! It comes up with a file reference "metres" long. Please rename to something like "Lookup_range" !

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    @John,

    Here it is.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    sorry for the late reply.

    yesterday i have try to download the file in mobile but cold not download the file.

    JohnTopley & oeldere: thanks you so much for your help.answer get it awesome and am really happy for your answer/reply.

    i can seen EXCEL FORUM the similar thread the name of: Vlookup same reference and return value if different:

    link:http://www.excelforum.com/excel-form...different.html.

    reply thread posted on 06-18-2013, 08:37 AM:Mr.Fotis1991 (Forum Moderator)

    the above mention thread is related/similar of my thread the same was resolve my thread if possible.

    MY THREAD:

    Point :1

    Invoice No/
    Material Doc No Part no. Quantity
    4903717231 9030-2110 50
    4903717231 26218264 2
    4903717231 26241589 5
    4903717231 26247149 3
    4903717231 7189-006 1
    4903717231 26218264 2
    4903717231 9007-495 30
    4903717231 9188-057 5
    4903717231 9188-022B 4
    4903717231 9188-022B 1
    4903717231 26217696 2
    4903717231 26218264 2
    4903717231 26710626 10
    4903717231 26218220 1
    4903717231 26948069A 3
    4903717231 26245621 4
    4903717231 26243123 50
    4903717231 26216316 1
    4903717231 26258785 4
    4903717231 26218490 1
    4903717231 26024332 15
    4903717231 28292808 1

    point ;2

    cancel Doc No.
    Material Doc No Part no. Quantity Vaue
    4903717231 26218264 2 1810.

    point:3

    Req Format:

    REQ FORMAT
    Material Doc No Part no. Quantity Req Format
    4903717231 9030-2110 50 0
    4903717231 26218264 2 1810
    4903717231 26241589 5 0
    4903717231 26247149 3 0
    4903717231 7189-006 1 0
    4903717231 26218264 2 0
    4903717231 9007-495 30 0
    4903717231 9188-057 5 0
    4903717231 9188-022B 4 0
    4903717231 9188-022B 1 0
    4903717231 26217696 2 0
    4903717231 26218264 2 0
    4903717231 26710626 10 0
    4903717231 26218220 1 0
    4903717231 26948069A 3 0
    4903717231 26245621 4 0
    4903717231 26243123 50 0
    4903717231 26216316 1 0
    4903717231 26258785 4 0
    4903717231 26218490 1 0
    4903717231 26024332 15 0
    4903717231 28292808 1 0
    4903720140 26218264 2 1810


    Point 1:In mentioned materiel doc No(invoice No)contain 22 line item.(whenever cancel the line item(Material Doc No,partno,Qty) i havementioned the color in Blue.

    Point 2 :some time cancel the particular line item(whenever cancel the line item(Material Doc No,partno,Qty) i have mentioned the color in red)


    Point 3:Sheet Name 2:Invoice No/material doc no-Original and cancel and sheet Name 1.cancel doc no.

    my query is the mentioned color against corresponding to return the value.

    please help me . file attached.
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    please help me.

  29. #29
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    You have enough information from all the correspondence to resolve this.

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    @silambarasanJ

    How about the result in #26?

    You don't reply on that.

  31. #31
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    sorry oeldere sir. am waiting for yours suggestion.

    i have replied on #27.value return is perfect and amazing tricks. thank you so much.

    why am again started thread the reason of please see my post #27.

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    The lookup.xlsx looks the same to me, as your earlier request.

    SO what is new on this question?

  33. #33
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    yes sir requested the same.

    My new question is without additional column if possible.

    The below mention thread link is related of my thread that thread is posted on excel forum without any additional column.

    i can seen EXCEL FORUM the similar thread the name of: Vlookup same reference and return value if different:

    link:http://www.excelforum.com/excel-form...different.html.

    reply thread posted on 06-18-2013, 08:37 AM:Mr.Fotis1991 (Forum Moderator)

    solved thread and file attached.

    also attached my Req file.

    This is my requested.not consider for the question.
    Attached Files Attached Files

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

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    Please make you question clear (from the beginning).

    You have a solution with helpcolumns, what is the problem with that, since you can hide the helpcolumns if you want.

  35. #35
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

    thanks you so much for your suggestion. i can follow up.

+ 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] VBA Vlookup Nth occurrence help
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2013, 09:33 PM
  2. VLOOKUP, wanting 2nd, 3rd occurrence, etc.
    By nj8988 in forum Excel General
    Replies: 4
    Last Post: 07-03-2012, 02:49 PM
  3. [SOLVED] Maybe: VLOOKUP with first occurrence of a value?
    By SueWithQuestion in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2012, 01:38 PM
  4. Vlookup and subtract when second occurrence of ID
    By transportplanner in forum Excel General
    Replies: 13
    Last Post: 06-16-2010, 09:23 PM
  5. vlookup -need 2nd occurrence
    By eds000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2006, 09:55 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