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.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/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"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]CUST[/TD]
[TD="align: center"]STATUS[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]ACCT[/TD]
[TD="align: center"]ACCT. NAME[/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD][/TD]
[TD]CGLE300[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CGLE300[/TD]
[TD]Final[/TD]
[TD]2017-09-28[/TD]
[TD]1000[/TD]
[TD]Customer Name[/TD]
[TD]Jo Bloggs Boozer[/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CGLE300[/TD]
[TD]Provisional[/TD]
[TD]2017-09-29[/TD]
[TD]1000[/TD]
[TD]Customer Name[/TD]
[TD]Jo Bloggs Boozer v2[/TD]
[TD][/TD]
[TD]Final[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CBOO600[/TD]
[TD]Final[/TD]
[TD]2017-09-29[/TD]
[TD]1000[/TD]
[TD]Customer Name[/TD]
[TD]Bargain Bonanza[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CBOO600[/TD]
[TD]Provisional[/TD]
[TD]2017-09-28[/TD]
[TD]1000[/TD]
[TD]Customer Name[/TD]
[TD]Bargain Booze v2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]CGLE300[/TD]
[TD]Provisional[/TD]
[TD]2017-09-28[/TD]
[TD]2001[/TD]
[TD]Invoice Address[/TD]
[TD]PO Box 9999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

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

1​

[/td][td]

CUST​

[/td][td]

STATUS​

[/td][td]

DATE​

[/td][td]

ACCT​

[/td][td]

ACCT. NAME​

[/td][td]

DESCRIPTION​

[/td][td][/td][td]CGLE300[/td][/tr]

[tr][td]

2​

[/td][td]CGLE300[/td][td]Final[/td][td]9/28/2017[/td][td]1000[/td][td]Customer Name[/td][td]Jo Bloggs Boozer[/td][td][/td][td]1000[/td][/tr]

[tr][td]

3​

[/td][td]CGLE300[/td][td]Provisional[/td][td]9/29/2017[/td][td]1000[/td][td]Customer Name[/td][td]Jo Bloggs Boozer v2[/td][td][/td][td]Final[/td][/tr]

[tr][td]

4​

[/td][td]CBOO600[/td][td]Final[/td][td]9/29/2017[/td][td]1000[/td][td]Customer Name[/td][td]Bargain Bonanza[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]CBOO600[/td][td]Provisional[/td][td]9/28/2017[/td][td]1000[/td][td]Customer Name[/td][td]Bargain Booze v2[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]CGLE300[/td][td]Provisional[/td][td]9/28/2017[/td][td]2001[/td][td]Invoice Address[/td][td]PO Box 9999[/td][td][/td][td][/td][/tr]
[/table]

[Table="width:, class:grid"][tr][td] [/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

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

1​

[/td][td]

CUST​

[/td][td]

STATUS​

[/td][td]

DATE​

[/td][td]

ACCT​

[/td][td]

ACCT. NAME​

[/td][td]

DESCRIPTION​

[/td][/tr]

[tr][td]

2​

[/td][td]CGLE300[/td][td]Final[/td][td]

43006​

[/td][td]

1000​

[/td][td]Customer Name[/td][td]Jo Bloggs Boozer[/td][/tr]
[/table]
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.