VLOOKUP with MULTIPLE CONDITION

[Harold Garcia]

Dear Expert,

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

Ticket Sequence	Validation Code	Amount	Date	Name
1234	8814	5000	Apri1	John
1222	1598	100	May2	Mark
1221	2456	150	Feb4	Luke
1234	6871	200	Mar10	Matthew

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​
1​	Ticket Sequence​	Validation Code​	Amount​	Date​	Name​
2​	1222​	1598​	100​	May2​	Mark​

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

	A​	B​	C​	D​	E​
1​	Ticket Sequence​	Validation Code​	Amount​	Date​	Name​
2​	1222​	1598​	100​	May2​	Mark​

<tbody>
</tbody>

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

A

B

C

D

E

F

I

J

K

L

M

N

1

Ticket Sequence

Validation Code

Amount

Date

Name

Sheet

1

Ticket Sequence

Validation Code

Amount

Date

Name

Sheet

2

1234

8814

5000

Apri1

John

Main

2

1234

8814

5000

Apri1

John

Sheet1

3

1234

6871

200

Mar10

Matthew

3

1222

1598

100

May2

Mark

4

4

1221

2456

150

Feb4

Luke

5

5

1234

6871

200

Mar10

Matthew

6

6

***

***************

***************

********

*******

*********

******

**

***

***************

***************

********

*******

*********

******

<tbody>
</tbody>

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.