VLOOKUP with MULTIPLE CONDITION

[Harold Garcia]

Dear Expert,

Can you help me find a formula that will solve my problem.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Ticket Sequence[/TD]
[TD]Validation Code[/TD]
[TD]Amount[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]8814[/TD]
[TD]5000[/TD]
[TD]Apri1[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]1222[/TD]
[TD]1598[/TD]
[TD]100[/TD]
[TD]May2[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]1221[/TD]
[TD]2456[/TD]
[TD]150[/TD]
[TD]Feb4[/TD]
[TD]Luke[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]6871[/TD]
[TD]200[/TD]
[TD]Mar10[/TD]
[TD]Matthew[/TD]
[/TR]
</tbody>[/TABLE]

Can your give me a formula that will satisfy this condition

1. From a separate worksheet I will encode the Ticket Sequence and Validation Code. The Amount, Date and Name will automatically appear.
2. I cannot use the vlookup since there are duplicate ticket sequence.
3. Is there any vlookup formula using multiple conditions.

Thank You

 

[Marcelo Branco]

Assuming data in Sheet1 columns A:E; headers in row 1

Try in another sheet

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Ticket Sequence​

[/td][td]

Validation Code​

[/td][td]

Amount​

[/td][td]

Date​

[/td][td]

Name​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1222​

[/td][td]

1598​

[/td][td="bgcolor:#D9D9D9"]

100​

[/td][td="bgcolor:#D9D9D9"]

May2​

[/td][td="bgcolor:#D9D9D9"]

Mark​

[/td][/tr]
[/table]

Array formula in C2 copied across
=INDEX(Sheet1!C$2:C$100,MATCH(1,IF(Sheet1!$A$2:$A$100=$A2,IF(Sheet1!$B$2:$B$100=$B2,1)),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Harold Garcia]

Assuming data in Sheet1 columns A:E; headers in row 1

Try in another sheet

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Ticket Sequence​

[/TD]
[TD]

Validation Code​

[/TD]
[TD]

Amount​

[/TD]
[TD]

Date​

[/TD]
[TD]

Name​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1222​

[/TD]
[TD]

1598​

[/TD]
[TD="bgcolor: #D9D9D9"]

100​

[/TD]
[TD="bgcolor: #D9D9D9"]

May2​

[/TD]
[TD="bgcolor: #D9D9D9"]

Mark​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in C2 copied across
=INDEX(Sheet1!C$2:C$100,MATCH(1,IF(Sheet1!$A$2:$A$100=$A2,IF(Sheet1!$B$2:$B$100=$B2,1)),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

Sir Marcelo,

Thanks for the reply but the formula dont work. Maybe my mistake.
From a separate worksheet what will be the formula i will use in C2,D2 and E2 to automatically transfer data from the source data. I will only encode the Ticket Sequence and validation Code.

Thank You

 

[Marcelo Branco]

The formula worked perfectly for me.
Have you confirmed the formula with Ctrl+Shift+Enter simultaneously?

M.

 

[markmzz]

Dear Expert,

Can you help me find a formula that will solve my problem.

Can your give me a formula that will satisfy this condition

1. From a separate worksheet I will encode the Ticket Sequence and Validation Code. The Amount, Date and Name will automatically appear.
2. I cannot use the vlookup since there are duplicate ticket sequence.
3. Is there any vlookup formula using multiple conditions.

Thank You

Hi!

Try the formula below too:

In C2 in the Main sheet and copy down and to the right

=INDEX(Sheet1!$C$2:$E$5,MATCH(2,MMULT(--(Sheet1!$A$2:$B$5=$A2:$B2),{1;1}),0),MATCH(C$1,Sheet1!$C$1:$E$1,0))


[TABLE="class: grid, width: 1006"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Ticket Sequence[/TD]
[TD="align: center"]Validation Code[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Sheet[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Ticket Sequence[/TD]
[TD="align: center"]Validation Code[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Sheet[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]8814[/TD]
[TD="align: center"]5000[/TD]
[TD="align: center"]Apri1[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]Main[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]8814[/TD]
[TD="align: center"]5000[/TD]
[TD="align: center"]Apri1[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]Sheet1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]6871[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]Mar10[/TD]
[TD="align: center"]Matthew[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1222[/TD]
[TD="align: center"]1598[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]May2[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1221[/TD]
[TD="align: center"]2456[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]Feb4[/TD]
[TD="align: center"]Luke[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]6871[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]Mar10[/TD]
[TD="align: center"]Matthew[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]******[/TD]
[/TR]
</tbody>[/TABLE]


By the way Marcelo's formula work for me.

Markmzz

 

[SpillerBD]

or

{=INDEX(C$2:C$5,MATCH($J3&$K3,$A$2:$A$5&$B$2:$B$5,0))}

Marcello's technique is new to me. I wouldn't be surprised if he has tested it against these others and has a good reason to use. Probably a little faster on big tables.
Mine is what Mike Girvin has written on and I think it is a little easier to follow the logic. It took me a moment to see why Marcello is matching a 1, its TRUE!

"...It Depends"

 

[Harold Garcia]

or

{=INDEX(C$2:C$5,MATCH($J3&$K3,$A$2:$A$5&$B$2:$B$5,0))}

Marcello's technique is new to me. I wouldn't be surprised if he has tested it against these others and has a good reason to use. Probably a little faster on big tables.
Mine is what Mike Girvin has written on and I think it is a little easier to follow the logic. It took me a moment to see why Marcello is matching a 1, its TRUE!

"...It Depends"

Im very sorry, but i cannot use all the given formula. My mistake.

 

[markmzz]

Im very sorry, but i cannot use all the given formula. My mistake.

Hi Harold,

Try this small modification in my formula (in red - put the name of the sheet between the character '):

=INDEX('Sheet 1'!$C$2:$E$5,MATCH(2,MMULT(--('Sheet 1'!$A$2:$B$5=$A2:$B2),{1;1}),0),MATCH(C$1,'Sheet 1'!$C$1:$E$1,0))

By the way, do the same in Marcelo's formula too.

Markmzz

 

[Harold Garcia]

Hi Harold,

Try this small modification in my formula (in red - put the name of the sheet between the character '):

=INDEX('Sheet 1'!$C$2:$E$5,MATCH(2,MMULT(--('Sheet 1'!$A$2:$B$5=$A2:$B2),{1;1}),0),MATCH(C$1,'Sheet 1'!$C$1:$E$1,0))

By the way, do the same in Marcelo's formula too.

Markmzz

Again thank you for not surrendering to help me. But unfortunately the formula wont work, i cannot follow or understand the formula. Is there any other way? like vlookup or macro?

 

[Marcelo Branco]

Again thank you for not surrendering to help me. But unfortunately the formula wont work, i cannot follow or understand the formula. Is there any other way? like vlookup or macro?

Could you, please, tell us the exact location of your data. To provide a formula that works we need to know: sheet name; rows and columns that contain the data you showed in your first message.

M.