VLOOKUPIFS, VLOOKUP with multiple conditions

[HOGGL]

Hi,

Hope someone can help. Going round in circles trying to work out how to do a lookup with multiple conditions.
Trying to lookup using the customer CGLE300 (H1) to return Description in column F if the ACCT. is 1000 (H2) and the Status is "Final" (H3).

Think i should be using vlookup and match functions, but can't seem to get one to work. Any ideas or pointers would be much appreciated.

	A	B	C	D	E	F	G	H
1	CUST	STATUS	DATE	ACCT	ACCT. NAME	DESCRIPTION		CGLE300
2	CGLE300	Final	2017-09-28	1000	Customer Name	Jo Bloggs Boozer		1000
3	CGLE300	Provisional	2017-09-29	1000	Customer Name	Jo Bloggs Boozer v2		Final
4	CBOO600	Final	2017-09-29	1000	Customer Name	Bargain Bonanza
5	CBOO600	Provisional	2017-09-28	1000	Customer Name	Bargain Booze v2
6	CGLE300	Provisional	2017-09-28	2001	Invoice Address	PO Box 9999

<tbody>
</tbody>

Thanks in advance.

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	CUST​	STATUS​	DATE​	ACCT​	ACCT. NAME​	DESCRIPTION​		CGLE300
2​	CGLE300	Final	9/28/2017	1000	Customer Name	Jo Bloggs Boozer		1000
3​	CGLE300	Provisional	9/29/2017	1000	Customer Name	Jo Bloggs Boozer v2		Final
4​	CBOO600	Final	9/29/2017	1000	Customer Name	Bargain Bonanza
5​	CBOO600	Provisional	9/28/2017	1000	Customer Name	Bargain Booze v2
6​	CGLE300	Provisional	9/28/2017	2001	Invoice Address	PO Box 9999

	J​	K​	L​	M​	N​	O​
1​	CUST​	STATUS​	DATE​	ACCT​	ACCT. NAME​	DESCRIPTION​
2​	CGLE300	Final	43006​	1000​	Customer Name	Jo Bloggs Boozer

J2=INDEX(A$2:A$6,MATCH($H$1&$H$2&$H$3,INDEX($A$2:$A$6&$D$2:$D$6&$B$2:$B$6,0),0))
copied across

This will only pull the 1st match it finds, if you have more than 1 match, you will need a different formula

 

[HOGGL]

Ford thanks so much for that!

If I did have more than one match and i wanted to take the most recent - without sorting it into date order so it picks up the first match - is there a way to do that?

 

[FDibbins]

bASED ON THAT SAME SAMPLE, CHANGE TO THIS...
=INDEX(A$2:A$7,MATCH($H$1&$H$2&$H$3&MAX($C$2:$C$7),INDEX($A$2:$A$7&$D$2:$D$7&$B$2:$B$7&$C$2:$C$7,0),0))
COPIED ACROSS

 

[Aladin Akyurek]

Is this what you are after?

=INDEX($F$2:$F$6,MATCH(MAX(IF($A$2:$A$6=H1,IF($B$2:$B$6=H3,IF($D$2:$D$6=H2,$C$2:$C$6)))),IF($A$2:$A$6=H1,IF($B$2:$B$6=H3,IF($D$2:$D$6=H2,$C$2:$C$6))),0))

which is confirmed with control+shift+enter, not just enter.

 

[HOGGL]

Thank you!

 

[Aladin Akyurek]

Thank you!

You are welcome.