LOOKUP FUNCTIONS & OTHER FUNCTIONS

Question 1

VLOOKUP

Answer 1

ROW GIVEN + CHOOSE COLUMN

Question 2

VLOOKUP - FORMULA

Answer 2

=VLOOKUP(ROW CRITERIA, TABLE ARRAY, COLUMN #,0)

Question 3

HLOOKUP

Answer 3

COLUMN GIVEN + CHOOSE ROW

Question 4

HLOOKUP - FORMULA

Answer 4

=HLOOKUP(COLUMN CRITERIA, TABLE ARRAY, ROW#,0)

Question 5

INDEX & MATCH

Answer 5

INDEX CREATES INDEX OF ALL POSSIBLE OUTPUTS

MATCH CREATES INTERSECTION BETWEEN ROW & COLUMNS

CREATE DROPDOWN LISTS FOR COLUMN & ROW INPUTS

Question 6

INDEX & MATCH - FORMULA

Answer 6

=INDEX(INDEX VALUES,MATCH(ROW CRITERIA, ROW ARRAY, 0),MATCH(COLUMN CRITERIA, COLUMN ARRAY, 0)

Question 7

SUMPRODUCT

Answer 7

SUMS THE PRODUCT OF MULTIPLE CELL RANGES

Question 8

SUMPRODUCT - FORMULA

Answer 8

=SUMPRODUCT(CR1, CR2)

CR = COLUMN RANGE

Question 9

RATE

Answer 9

INTEREST RATE BASED ON PV, FV, PMT AND TIME

Question 10

RATE - FORMULA

Answer 10

=RATE(PERIODS, PMT, PV, FV,0)

Question 11

ROUND - FORMULA

Answer 11

=ROUND(NUMBER, ROUNDING DIGITS)

Question 12

ROUNDUP - FORMULA

Answer 12

=ROUNDUP(NUMBER, ROUNDING DIGITS)

Question 13

ROUNDDOWN - FORMULA

Answer 13

=ROUNDDOWN(NUMBER, ROUNDING DIGITS)

Question 14

ROUNDING DIGITS

Answer 14

BASED ON MOVEMENT FROM END OF THE WHOLE NUMBER

1,000 - TO HUNDREDS: -2

1,045.655 - TO TENS: -1

.2548 - TO HUNDREDTHS: -1

.255485 - TO THOUSANDTHS: -2

Question 15

DATEDIF

Answer 15

SHOW # OF DAYS, MONTHS, YEARS BETWEEN START DATE & END DATE

Question 16

DATEDIF - FORMULA

Answer 16

=DATEDIF(START DATE, END DATE, CRITERIA)

Question 17

DATEDIF - CRITERIA

Answer 17

DAYS = “D”

MONTHS = “M”

YEARS = “Y”

Question 18

DATEDIF + 1

Answer 18

ADD 1 TO END OF DATEDIF FORMULA TO INCLUDE START DATE IN THE CALCULATION

Question 19

YEARFRAC

Answer 19

CALCULATES THE FRACTION OF YEAR BASED ON START DATE & END DATE

Question 20

YEARFRAC - FORMULA

Answer 20

=YEARFRAC(START DATE, END DATE)

Question 21

EOMONTH

Answer 21

PROVIDES END OF MONTH BASED ON CURRENT MONTH & MONTHS BEFORE OR AFTER IT

Question 22

EOMONTH - FORMULA

Answer 22

=EOMONTH(START DATE, # MONTHS BEFORE/AFTER)