Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |