Question

What is the formula? Use a VLOOKUP function in cell F25 of the CatastropheCoverageLowActivity worksheet to determine the appropriate Copayment for the first visit to a Primary Doctor. The Type in cell D25 is one of the parameters. Use absolute and relative references when appropriate

UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 -Monthly prenium $160-Deductible $6,600-0ut-ol-pocket maximum $6,600 Copayment Primary doctor-$50 Copayment Specialist Dactor $100 Ernergency Room No charge after deductible Hospital Services No charge after deductible Lab Services No chargeafter doductible Assignment Tasks Health Insurance Monthly premium Maximum out-ofpocket 5 160 6,500 Use a VLOOKUP function in cell F25 of the CatastrophrCoveragclowActivity worksheet to determine the appropriate Copayment for the first visit to a Primary Doctor. The "Type" in cell D25 is one of the 12 13 14 1 Type Deductible Co-Pay arameters. Use absolute and relative references when a rlate [1 Primary doctor Other doctors Emergency ropm 16 17 C $ 6,600$ 19 Total Premium Expense Total Medical Payments 21 al Medical Cost Cost after limit to MAX out of Potential Cost M 23 24 25 26 27 28 Descriptian Type Amount Capayment Deductible Coinsurance to Insured January Primary Doctor Emergency Room & Lab tests Dermatologist officevisit Primary Doctor Uralogist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist affice visit G24 C $ 1,707 В $ 366 B $545 B S175 B 250 April 31 August Drcembe Primary Doctor Totals 37 38

Answer 1

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

This function is used to lookup a value in a table by matching on the first column
It returns the matched value from a table.
Syntax
=VLOOKUP (value, table, col_index, [range_lookup])
Arguments
value - The value to look for in the first column of a table.
table - The table from which to retrieve a value.
col_index - The column in the table from which to retrieve a value.
range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

Example syntax is VLOOKUP(D25, D14:G17, 3,FALSE)
Here we should use absolute reference for the table, so that we can copy the formula by dragging the cell easily (without changing the lookup table cell range )
So, the parameters will be as below
value - D25
table - should be $D$14:$G$17
col_index - 3 as co-Pay value is in 3rd column
range_lookup - FALSE as we want exact match

So, the desired formula for F25 cell is

=VLOOKUP(D25,$D$14:$G$17,3,FALSE)
Using absolute reference in table is safer side. However for a particular cell the formula with relative reference will work also. Hence if we write the formula of F25 cell as =VLOOKUP(D25, D14:G17, 3, FALSE) , it will give correct result.

The formula will return 50.

Attaching an image for your better understanding

Excel VLOOKUP Function E4 fVLOOKUP(D4,B8:F17,4,FALSE) A B Lookup Email [email protected] ve ID 622 Resul+ 5 4 5 Department ID First 610 Janet 798 Steven 841 Evelyn 886 Marilyn 622 JonathanAdder 601 Adrian 869 Julie 867 Erica 785 Harold 648 Sharyn Email [email protected] [email protected] Sales [email protected] Fulfillment [email protected] Fulfillment [email protected] [email protected] Last Farle Batista Monet Bradle Fulfillment 10 Marketing Engineering Marketing Fulfillment 12 13 14 15 16 17 18 19 Look 20 olumn Birt Irons Tan Clayton Castor [email protected] [email protected] [email protected] Fulfillment [email protected] Support Resul+ columnEXCELJET