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

1. 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.

3. 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. 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. 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. Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

See attached:

7. 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)),"")

8. 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.

9. 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. 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.

11. 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. Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

Mark the related cells e.g. with the same color.

13. 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.

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

4903724826 26249929 1 2520

16. 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)

17. 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. 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. 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. 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. 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!

22. 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.

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

@John Topley,

see attachement.

25. 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" !

@John,

Here it is.

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

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

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

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.

29. 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. Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

@silambarasanJ

How about the result in #26?

31. 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. 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. 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.

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

also attached my Req file.

This is my requested.not consider for the question.

34. 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. Re: When vlookup ragnes is mutiple: How do vlookup value finding occurrence against coress

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

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