Vlookup with multiple conditions

[Abgar]

Hi all,

Hoping you can help here please. I have a table on sheet called "RawData". This sheet contains multiple rows / columns of data, and i am looking to use a Vlookup to:

* Lookup a value in column B of the table. This table may contain multiple entries which match in column B, but each would have a different value in column 5.
* I then want to crossreference the column with a specific value in column, and if found, then return a date contained in column 4.

I can't edit the data in the table, and so can't create a helper row in this table to use a Vlookup.

[TABLE="width: 456"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Owner[/TD]
[TD]Ref Number[/TD]
[TD]Client[/TD]
[TD]Due Date[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD]TOM[/TD]
[TD]WER0006001[/TD]
[TD][/TD]
[TD]23/10/2019[/TD]
[TD]Second Action[/TD]
[/TR]
[TR]
[TD]TOM[/TD]
[TD]WER0006001[/TD]
[TD][/TD]
[TD]29/10/2019[/TD]
[TD]First Action[/TD]
[/TR]
[TR]
[TD]TOM[/TD]
[TD]WER0006001[/TD]
[TD][/TD]
[TD]14/10/2019[/TD]
[TD]Third Action[/TD]
[/TR]
</tbody>[/TABLE]

So, if using the above as an example, i need a formula that i can input in a random cell of my choosing that will find where "Ref Number" = WER0006001 & "Action = First Action", and then populate the corresponding date (i.e. 29/10/2019).

I've seen some information on using a Match / Index, which results in an array formula, but i can't get my head around the parameters to use (i.e how to actually use it). Can someone provide an example of how i could use it in the above? Or an alternative approach i could use?

Also worth noting, the formula would be used in excess of 200 times in one workbook, so im not sure if an array formula would be suitable?

Appreciate any help you can give.

**Edit - sorry, Im on a work computer, so I can't figure out to properly input a table, so hopefully the above shows ok.

Cheers,
ABGar

 

[thespardian]

Assuming that your lookup cell is A6 and you are entering formula in B6.

concatenating the cell will help you.

=INDEX($D$2:$D$4,MATCH(A6,$B$2:$B$4&$E$2:$E$4,0))

The blue part of the formula is.... the target data you want to extract.
The Green part of the formula is ..... text or cell reference -- for the match you are looking for
The red part of the formula is ...... range in which you have to lookup.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	I	J	K
1	Owner	Ref Number	Client	Due Date	Action			WER0006001
2	TOM	WER0006001		23/10/2019	Second Action			First Action	29/10/2019
3	TOM	WER0006001		29/10/2019	First Action
4	TOM	WER0006001		14/10/2019	Third Action
5
Lookup

Cell Formulas
Range	Formula
K2	=INDEX(D2:D4,MATCH(J1&"|"&J2,INDEX(B2:B4&"|"&E2:E4,0),0))