combine vlookup and match help

[cadustin]

I need help, and fast! I am trying to create a form, and my formula is not
working! I have been looking at thread after thread and no luck... I still
get the #N/A every time.

Okay, on sheet 1 (Insurance Form) is where I need my information to
populate. All of my data is on sheet 2 (Look up sheet).

I have a drop down to tell me my benefit choice and my coverage choice. For
my cost, I want excel to look at sheet 2 and tell me what is in the cell for
those two choices. Here is my data:

It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5
for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form"

On my "Look Up Sheet"

A B C D
E
1 HMO PPO DENTAL VISION
2 EE 10.00 15.00 5.00
3.00
3 EE&SP 15.00 20.00 10.00 7.00
4 EE&CH 13.00 17.00 7.00 5.00
5 EE&FA 20.00 25.00 15.00 10.00

I have tried this formula, and others, but keep coming back with the same
error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for
LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look
Up Sheet'!$A$2:$E$5,0))

 

[Luke M]

Wrong function.

=INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up
Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0))

 

[cadustin]

I love you! I have been looking and reading every thread I can, and you did
it!!!

Thank you so much Luke M!!

 

[cadustin]

Luke M, I do have one more question. As I am putting this formula in several
cells, is there a way to prevent the #N/A from appearing in the rows that do
not meet the criteria? Here is an example:

BENEFIT COVERAGE COST
HMO Employee & Family 855.42
DENTAL Employee & Family 53.69
#N/A
#N/A
#N/A
#N/A

 

[Jacob Skaria]

Your approach is absolutely correct; using VLOOKUP() and MATCH()

=VLOOKUP(B5,'Look Up Sheet'!$A$1:$E$5,
MATCH(A5,'Look Up Sheet'!$A$1:$E$1,0),0)

Now to avoid the error NA# you can use ISNA()

=IF(ISNA(your formula),"",your formula)

 

[cadustin]

Not working Jacob, I am using the INDEX and MATCH as listed below.

 

[Jacob Skaria]

Try the below test...

Col A Col B Col C Col D
X Y Z
A 10 40 70
B 20 50 80
C 30 60 90

Replace strings with cell references

=VLOOKUP("A",A14,MATCH("Y",A11,0),0)