|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 17th, 2010, 07:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF INDEX and MATCH function together
Okay, now that I had the other figured out, I am told there is more I need.
I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35 
|
|
#2
March 17th, 2010, 07:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF INDEX and MATCH function together
Try this
=IF($F$2="FMLA", INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0)), INDEX('Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))) -- HTH Bob "cadustin" wrote in message ... Okay, now that I had the other figured out, I am told there is more I need. I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35
|
|
#3
March 17th, 2010, 08:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF INDEX and MATCH function together
I thought that was it. It works for the "others" but not if "FMLA" is in F2.
I get the #NA error. Also, Bob, can you tell me when we get this going right, how to set it up to not have an #NA if I don't have anything for that row? DATE OF LEAVE: TYPE OF LEAVE: CFRA CURRENT BENEFITS: BENEFIT COVERAGE COST Per PP ACTIONS COMMENTS HMO Employee & Child 524.93 #N/A #N/A "Bob Phillips" wrote: Try this =IF($F$2="FMLA", INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0)), INDEX('Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))) -- HTH Bob "cadustin" wrote in message ... Okay, now that I had the other figured out, I am told there is more I need. I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35 .
|
|
#4
March 17th, 2010, 10:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF INDEX and MATCH function together
I must admit I am struggling to give you a definitive answer here because as
I see it, the check cells(A5, B5) are slap bang in the middle of the data, and one of them is an amount. This just doesn't ring true, and I also have no idea of what is on Look Up Sheet. Generally, the #N/A meets no match, so one of the MATCH clauses is not matching, and you normally test this like so IF(ISNA(MATCH(...)),"some value",full_formula). Can you post a workbook on a file sharing site somewhere to take a look at it. -- HTH Bob "cadustin" wrote in message ... I thought that was it. It works for the "others" but not if "FMLA" is in F2. I get the #NA error. Also, Bob, can you tell me when we get this going right, how to set it up to not have an #NA if I don't have anything for that row? DATE OF LEAVE: TYPE OF LEAVE: CFRA CURRENT BENEFITS: BENEFIT COVERAGE COST Per PP ACTIONS COMMENTS HMO Employee & Child 524.93 #N/A #N/A "Bob Phillips" wrote: Try this =IF($F$2="FMLA", INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0)), INDEX('Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))) -- HTH Bob "cadustin" wrote in message ... Okay, now that I had the other figured out, I am told there is more I need. I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35 .
|
|
#5
March 19th, 2010, 07:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF INDEX and MATCH function together
Bob, I don't know where to post it, is there a way to send it to you? I don't
see any attachements here. "Bob Phillips" wrote: I must admit I am struggling to give you a definitive answer here because as I see it, the check cells(A5, B5) are slap bang in the middle of the data, and one of them is an amount. This just doesn't ring true, and I also have no idea of what is on Look Up Sheet. Generally, the #N/A meets no match, so one of the MATCH clauses is not matching, and you normally test this like so IF(ISNA(MATCH(...)),"some value",full_formula). Can you post a workbook on a file sharing site somewhere to take a look at it. -- HTH Bob "cadustin" wrote in message ... I thought that was it. It works for the "others" but not if "FMLA" is in F2. I get the #NA error. Also, Bob, can you tell me when we get this going right, how to set it up to not have an #NA if I don't have anything for that row? DATE OF LEAVE: TYPE OF LEAVE: CFRA CURRENT BENEFITS: BENEFIT COVERAGE COST Per PP ACTIONS COMMENTS HMO Employee & Child 524.93 #N/A #N/A "Bob Phillips" wrote: Try this =IF($F$2="FMLA", INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0)), INDEX('Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))) -- HTH Bob "cadustin" wrote in message ... Okay, now that I had the other figured out, I am told there is more I need. I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35 . .
|
|
#6
March 19th, 2010, 08:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF INDEX and MATCH function together
You can upload your workbook to one of these sites.
http://www.freefilehosting.net/ http://savefile.com/ After uploading, post the access URL you were given Someone may have a look at it. Gord Dibben MS Excel MVP On Fri, 19 Mar 2010 12:23:01 -0700, cadustin wrote: Bob, I don't know where to post it, is there a way to send it to you? I don't see any attachements here. "Bob Phillips" wrote: I must admit I am struggling to give you a definitive answer here because as I see it, the check cells(A5, B5) are slap bang in the middle of the data, and one of them is an amount. This just doesn't ring true, and I also have no idea of what is on Look Up Sheet. Generally, the #N/A meets no match, so one of the MATCH clauses is not matching, and you normally test this like so IF(ISNA(MATCH(...)),"some value",full_formula). Can you post a workbook on a file sharing site somewhere to take a look at it. -- HTH Bob "cadustin" wrote in message ... I thought that was it. It works for the "others" but not if "FMLA" is in F2. I get the #NA error. Also, Bob, can you tell me when we get this going right, how to set it up to not have an #NA if I don't have anything for that row? DATE OF LEAVE: TYPE OF LEAVE: CFRA CURRENT BENEFITS: BENEFIT COVERAGE COST Per PP ACTIONS COMMENTS HMO Employee & Child 524.93 #N/A #N/A "Bob Phillips" wrote: Try this =IF($F$2="FMLA", INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0)), INDEX('Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))) -- HTH Bob "cadustin" wrote in message ... Okay, now that I had the other figured out, I am told there is more I need. I need a formula that tells me to look at one place IF my criteria is met, otherwise look here for my information: Here is my formula that is returning FALSE or #REF!: =IF($F$2="FMLA",(INDEX('Look Up Sheet'!$B$2:$E$5,MATCH($B$5,'Look Up Sheet'!$A$2:$A$5,0),MATCH($A$5,$B$1:$E$1,0),INDEX( 'Look Up Sheet'!$B$6:$E$9,MATCH(B5,'Look Up Sheet'!$A$6:$A$9,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))))) This is my scenario... I have 2 worksheets, one is my form and the other is my lookup for all information. I want to look at F2 to see if it says FMLA or CFRA (or any other type of leave). If it says FMLA, I want it to look at the other two matches and give me the corresponding amounts, if not, give me the corresponding amounts in the other section I specified. Here is my data: A B C E E F 1 HMO PPO DENTAL VISION 2 Employee Only $- $22.19 $15.32 $5.06 FMLA 3 Employee & Spouse $110.85 $159.67 $29.50 $8.57 4 Employee & Child $73.47 $116.75 $31.00 $8.65 5 Employee & Family $181.88 $249.67 $53.69 $14.35 6 Employee Only $269.20 $291.39 $17.46 $5.06 OTHER 7 Employee & Spouse $592.24 $641.06 $33.43 $8.57 8 Employee & Child $524.93 $568.20 $33.02 $8.65 9 Employee & Family $855.42 $890.20 $58.05 $14.35 . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
