IF and VLOOKUP

[ziak]

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.

 

[Pete_UK]

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,0))

and then copy this down for your 500 employees.

Hope this helps.

Pete

 

[Eduardo]

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