IF and VLOOKUP
 

Hello:

Here's my array:

A B C
1 Band Service Target
2 6 SAP 95%
3 7 SAP 96%
4 8 SAP 97%
5 9 SAP 80%
6 10 SAP 70%
7 AP SAP 45%
8 6 ORA 93%
9 7 ORA 92%
10 8 ORA 95%
11 9 ORA 90%
12 10 ORA 70%
13 AP ORA 20%
14 6 OTH 80%
15 7 OTH 85%
16 8 OTH 90%
17 9 OTH 85%
18 10 OTH 60%
19 AP OTH 15%

I have a list of 500 people that belong to either SAP, ORA or OTH and are of
various Bands. Based on their bands and service I need to pull in the target.

How do I do that with VLOOKUP and IF functions? Much appreciated.

IF and VLOOKUP
 

In column D you can join the Service and Band together like this:

=B2&A2

and copy this down.

Then you can use an INDEX/MATCH combination to get Target from that.
If in your main sheet you have Band in column F and Service in column
H (for example), then you can use this:

=INDEX(Sheet2!C:C,MATCH(H2&F2,Sheet2!D:D,0))

and then copy this down for your 500 employees.

Hope this helps.

Pete

On Mar 24, 3:09*pm, ziak wrote:
Hello:

Here's my array:

* * * A * * * *B * * * * * *C
1 * *Band * Service * Target
2 * *6 * * * * SAP * * * *95%
3 * *7 * * * * SAP * * * *96%
4 * *8 * * * * SAP * * * *97%
5 * *9 * * * * SAP * * * *80%
6 * 10 * * * *SAP * * * *70%
7 * AP * * * SAP * * * * 45%
8 * 6 * * * * ORA * * * *93%
9 * 7 * * * * ORA * * * *92%
10 *8 * * * *ORA * * * *95%
11 *9 * * * *ORA * * * *90%
12 *10 * * *ORA * * * *70%
13 *AP * * *ORA * * * 20%
14 * 6 * * * OTH * * * *80%
15 * 7 * * * OTH * * * *85%
16 *8 * * * *OTH * * * *90%
17 *9 * * * *OTH * * * *85%
18 *10 * * *OTH * * * *60%
19 *AP * * *OTH * * * 15%

I have a list of 500 people that belong to either SAP, ORA or OTH and are of
various Bands. *Based on their bands and service I need to pull in the target.

How do I do that with VLOOKUP and IF functions? *Much appreciated.

IF and VLOOKUP
 

Hi,
I assume you enter your band in G2, Service in H2 and you want the target in
I3 so in I3 enter

=INDEX(C2:C4,MATCH(G2,A2:A4,0),MATCH(H2,B2:B4,0))

change range to fit your needs

"ziak" wrote:

Hello:

Here's my array:

A B C
1 Band Service Target
2 6 SAP 95%
3 7 SAP 96%
4 8 SAP 97%
5 9 SAP 80%
6 10 SAP 70%
7 AP SAP 45%
8 6 ORA 93%
9 7 ORA 92%
10 8 ORA 95%
11 9 ORA 90%
12 10 ORA 70%
13 AP ORA 20%
14 6 OTH 80%
15 7 OTH 85%
16 8 OTH 90%
17 9 OTH 85%
18 10 OTH 60%
19 AP OTH 15%

I have a list of 500 people that belong to either SAP, ORA or OTH and are of
various Bands. Based on their bands and service I need to pull in the target.

How do I do that with VLOOKUP and IF functions? Much appreciated.