combine vlookup and match help

C

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

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

cadustin

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

Thank you so much Luke M!!
 
C

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
 
J

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

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",A1:D4,MATCH("Y",A1:D1,0),0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top