VLOOKUP with MULTIPLE CONDITION

H

Harold Garcia

Board Regular
Joined
Jan 2, 2014
Messages
72
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
 
Sort by date Sort by votes
Marcelo Branco said:
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.
Click to expand...
This is the Source Data with a sheet name "PROCESS SHEET"

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tkt Seq[/TD]
[TD]Issue By[/TD]
[TD]Issued[/TD]
[TD]Validation Code[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4776[/TD]
[TD]1062[/TD]
[TD]May 1[/TD]
[TD]6352[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1687[/TD]
[TD]9102[/TD]
[TD]Feb 1[/TD]
[TD]1234[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4776[/TD]
[TD]1009[/TD]
[TD]Apr 8[/TD]
[TD]9114[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1245[/TD]
[TD]9102[/TD]
[TD]Mar 9[/TD]
[TD]0309[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


This will be the Working Data with a sheet name "ENCODE"

[TABLE="class: grid, width: 100"]
<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]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tkt Seq[/TD]
[TD]Issue By[/TD]
[TD]Issued[/TD]
[TD]Validation Code[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4776[/TD]
[TD]1062[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1687[/TD]
[TD]9102[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4776[/TD]
[TD]1009[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1245[/TD]
[TD]9102[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
</tbody>[/TABLE]


Thank you So Much. God Bless
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try

Sheet ENCODE


[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]
Tkt Seq​
[/td][td]
Issue By​
[/td][td]
Issued​
[/td][td]
Validation Code​
[/td][td]
Amount​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
4776​
[/td][td]
1062​
[/td][td="bgcolor:#D9D9D9"]
May 1​
[/td][td="bgcolor:#D9D9D9"]
6352​
[/td][td="bgcolor:#D9D9D9"]
10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
1687​
[/td][td]
9102​
[/td][td="bgcolor:#D9D9D9"]
Feb 1​
[/td][td="bgcolor:#D9D9D9"]
1234​
[/td][td="bgcolor:#D9D9D9"]
15​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
4776​
[/td][td]
1009​
[/td][td="bgcolor:#D9D9D9"]
Apr 8​
[/td][td="bgcolor:#D9D9D9"]
9114​
[/td][td="bgcolor:#D9D9D9"]
200​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
1245​
[/td][td]
9102​
[/td][td="bgcolor:#D9D9D9"]
Mar 9​
[/td][td="bgcolor:#D9D9D9"]
309​
[/td][td="bgcolor:#D9D9D9"]
1000​
[/td][/tr]
[/table]

Array formula in C2 copied across and down (gray area)
=INDEX('PROCESS SHEET'!O$8:O$100,MATCH(1,IF('PROCESS SHEET'!$M$8:$M$100=$A2,IF('PROCESS SHEET'!$N$8:$N$100=$B2,1)),0))
confirmed with Ctrl+Shift+Enter

M.
 
Upvote 0
Harold Garcia said:
This is the Source Data with a sheet name "PROCESS SHEET"
Click to expand...
Hi!

Try the formulas below too:

In C2 and copy down and to the right

=INDEX('PROCESS SHEET'!$O$8:$Q$11,MATCH(2,MMULT(--('PROCESS SHEET'!$M$8:$N$11=$A2:$B2),{1;1}),0),MATCH(C$1,'PROCESS SHEET'!$O$7:$Q$7,0))

Or

In C2 and copy down and to the right​

Array Formula - use Ctrl+Shift+Enter to enter the formula

=VLOOKUP($B2,IF('PROCESS SHEET'!$M$8:$M$11=$A2,'PROCESS SHEET'!$N$8:$Q$11),MATCH(C$1,'PROCESS SHEET'!$N$7:$Q$7,0),0)


[TABLE="class: grid, width: 1124"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tkt Seq[/TD]
[TD]Issue By[/TD]
[TD]Issued[/TD]
[TD]Validation Code[/TD]
[TD]Amount[/TD]
[TD]Sheet[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Tkt Seq[/TD]
[TD]Issue By[/TD]
[TD]Issued[/TD]
[TD]Validation Code[/TD]
[TD]Amount[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]4776[/TD]
[TD="align: right"]1062[/TD]
[TD]May 1[/TD]
[TD="align: right"]6352[/TD]
[TD="align: right"]10[/TD]
[TD]ENCORE[/TD]
[TD][/TD]
[TD]8[/TD]
[TD="align: right"]4776[/TD]
[TD="align: right"]1062[/TD]
[TD]May 1[/TD]
[TD="align: right"]6352[/TD]
[TD="align: right"]10[/TD]
[TD]PROCESS SHEET[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1687[/TD]
[TD="align: right"]9102[/TD]
[TD]Feb 1[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD="align: right"]1687[/TD]
[TD="align: right"]9102[/TD]
[TD]Feb 1[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]4776[/TD]
[TD="align: right"]1009[/TD]
[TD]Apr 8[/TD]
[TD="align: right"]9114[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD="align: right"]4776[/TD]
[TD="align: right"]1009[/TD]
[TD]Apr 8[/TD]
[TD="align: right"]9114[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]9102[/TD]
[TD]Mar 9[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]9102[/TD]
[TD]Mar 9[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***************[/TD]
[TD]********[/TD]
[TD]***************[/TD]
[TD]*********[/TD]
[TD]********[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***************[/TD]
[TD]********[/TD]
[TD]***************[/TD]
[TD]*********[/TD]
[TD]**************[/TD]
[/TR]
</tbody>[/TABLE]


I hope that the formulas below helps.

Markmzz
 
Last edited:
Upvote 0
markmzz said:
hi!

Try the formulas below too:

In c2 and copy down and to the right

=index('process sheet'!$o$8:$q$11,match(2,mmult(--('process sheet'!$m$8:$n$11=$a2:$b2),{1;1}),0),match(c$1,'process sheet'!$o$7:$q$7,0))

or

in c2 and copy down and to the right​

array formula - use ctrl+shift+enter to enter the formula

=vlookup($b2,if('process sheet'!$m$8:$m$11=$a2,'process sheet'!$n$8:$q$11),match(c$1,'process sheet'!$n$7:$q$7,0),0)


[table="class: Grid, width: 1124"]
<tbody>[tr]
[td][/td]
[td]a[/td]
[td]b[/td]
[td]c[/td]
[td]d[/td]
[td]e[/td]
[td]f[/td]
[td][/td]
[td][/td]
[td]m[/td]
[td]n[/td]
[td]o[/td]
[td]p[/td]
[td]q[/td]
[td]r[/td]
[/tr]
[tr]
[td]1[/td]
[td]tkt seq[/td]
[td]issue by[/td]
[td]issued[/td]
[td]validation code[/td]
[td]amount[/td]
[td]sheet[/td]
[td][/td]
[td]7[/td]
[td]tkt seq[/td]
[td]issue by[/td]
[td]issued[/td]
[td]validation code[/td]
[td]amount[/td]
[td]sheet[/td]
[/tr]
[tr]
[td]2[/td]
[td="align: Right"]4776[/td]
[td="align: Right"]1062[/td]
[td]may 1[/td]
[td="align: Right"]6352[/td]
[td="align: Right"]10[/td]
[td]encore[/td]
[td][/td]
[td]8[/td]
[td="align: Right"]4776[/td]
[td="align: Right"]1062[/td]
[td]may 1[/td]
[td="align: Right"]6352[/td]
[td="align: Right"]10[/td]
[td]process sheet[/td]
[/tr]
[tr]
[td]3[/td]
[td="align: Right"]1687[/td]
[td="align: Right"]9102[/td]
[td]feb 1[/td]
[td="align: Right"]1234[/td]
[td="align: Right"]15[/td]
[td][/td]
[td][/td]
[td]9[/td]
[td="align: Right"]1687[/td]
[td="align: Right"]9102[/td]
[td]feb 1[/td]
[td="align: Right"]1234[/td]
[td="align: Right"]15[/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td="align: Right"]4776[/td]
[td="align: Right"]1009[/td]
[td]apr 8[/td]
[td="align: Right"]9114[/td]
[td="align: Right"]200[/td]
[td][/td]
[td][/td]
[td]10[/td]
[td="align: Right"]4776[/td]
[td="align: Right"]1009[/td]
[td]apr 8[/td]
[td="align: Right"]9114[/td]
[td="align: Right"]200[/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td="align: Right"]1245[/td]
[td="align: Right"]9102[/td]
[td]mar 9[/td]
[td="align: Right"]309[/td]
[td="align: Right"]1000[/td]
[td][/td]
[td][/td]
[td]11[/td]
[td="align: Right"]1245[/td]
[td="align: Right"]9102[/td]
[td]mar 9[/td]
[td="align: Right"]309[/td]
[td="align: Right"]1000[/td]
[td][/td]
[/tr]
[tr]
[td]6[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]12[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]***[/td]
[td]***************[/td]
[td]***************[/td]
[td]********[/td]
[td]***************[/td]
[td]*********[/td]
[td]********[/td]
[td]**[/td]
[td]***[/td]
[td]***************[/td]
[td]***************[/td]
[td]********[/td]
[td]***************[/td]
[td]*********[/td]
[td]**************[/td]
[/tr]
</tbody>[/table]


i hope that the formulas below helps.

Markmzz
Click to expand...

at last! Now it works! Thank you so much. More power and god bless!
 
Upvote 0
Hai friend..
this discussion is I'm looking for. But, without example of workbook its difficult to me understand.
Kindly some one give the sample excel related to the above table where is discussed. Hopefully could.. :)
 
Upvote 0
adrin said:
Hai friend..
this discussion is I'm looking for. But, without example of workbook its difficult to me understand.
Kindly some one give the sample excel related to the above table where is discussed. Hopefully could.. :)
Click to expand...

Hi!

Try to copy the tables of the post #13 and then use the formulas of the same post.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Multiple a dynamic Range based on conditions
Replies
0
Views
854
Pepperoni
P
A
  • Question Question
Vlookup with multiple conditions
Replies
2
Views
985
Fluff
Fluff
N
  • Question Question
Multiple Conditional Formatting Rules in VBA
Replies
3
Views
9K
Fluff
Fluff
V
  • Question Question
Vlookup Multiple Output
2
Replies
15
Views
1K
Aladin Akyurek
Aladin Akyurek
R
  • Question Question
Formula for Multiple IFs condition
Replies
9
Views
1K
jtakw
jtakw

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top