IF INDEX and MATCH function together

[cadustin]

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

 

[Bob Phillips]

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

 

[cadustin]

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]

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.

 

[cadustin]

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.

 

[Gord Dibben]

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

 

[cadustin]

Thank you for your help! And others who gave me information as well. Here is
my document, I hope someone can fix it!

Insurance Premiums for leaves.xls

 

[cadustin]

It looks like after playing around with Bob's formula I may have it working,
unfortunately, when I copy it down to the next two fields, it is just
repeating what I put in the first one.

Thank you for your help! And others who gave me information as well. Here is
my document, I hope someone can fix it!

Insurance Premiums for leaves.xls

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



.