Match & Index with specific criteria

[drewberts]

I'll simplify the problem I'm having with an example.

I have 2 sheets the first (called CLIENTS) has FIRSTNAME, LASTNAME, COMPANY, TYPE, STATUS
The second (called JOBS) has FIRSTNAME, LASTNAME, COMPANY, TYPE, SITE, POSTCODE

The idea is that the first sheet is filled in with clients and in the STATUS column the answer is either YES or NO (I'm trying to differentiate between active and non active clients).
On the second sheet I complete the FIRSTNAME and LASTNAME and the COMPANY and TYPE columns are auto populated from the CLIENT sheet using:-

=INDEX(Clients!$C$8:$C$2300,MATCH($A1&$B1,Clients!$A$8:$A$2300&Clients!$B$8:$B$2300,0))
=INDEX(Clients!$D$8:$D$2300,MATCH($A1&$B1,Clients!$A$8:$A$2300&Clients!$B$8:$B$2300,0))

These both work fine. However what I wan't to introduce now is that they only auto-populate if the STATUS in the CLIENT sheet is set to YES.

Hope that makes sense.

 

[shuvodey]

Hi...

I took the liberty of rearranging your data on one sheet purely for the ease of demonstration purpose:

A

B

C

D

E

F

G

H

I

J

K

L

M

4

CLIENTS

JOBS

5

FIRSTNAME

LASTNAME

COMPANY

TYPE

STATUS

FIRSTNAME

LASTNAME

COMPANY

TYPE

SITE

POSTCODE

6

A

D

X

C1

YES

A

D

7

B

E

Y

C2

NO

B

E

8

C

F

Z

C3

YES

C

F

<tbody>
</tbody>

I entered the following array formulas to conditionally populate:

Company in J6

=IF(INDEX($F$6:$F$8,MATCH(H6&I6,$B$6:$B$8&$C$6:$C$8,0),1)="Yes",INDEX($D$6:$D$8,MATCH(H6&I6,$B$6:$B$8&$C$6:$C$8,0),1),"")

Type in J7

=IF(INDEX($F$6:$F$8,MATCH(H6&I6,$B$6:$B$8&$C$6:$C$8,0),1)="Yes",INDEX($E$6:$E$8,MATCH(H6&I6,$B$6:$B$8&$C$6:$C$8,0),1),"")

Hope this helps.

 

[drewberts]

Many thanks - works a treat