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.

Please help me

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"?

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.

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

See attached:

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.

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.

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.

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

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.

Please help me

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)

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

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

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.

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.

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!

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

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

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.

please help me.

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

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.

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.

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