VLOOKUP HLOOKUP INDEX MATCH

A DETAILED PRESENTATION ON VLOOKUP HLOOKUP INDEX MATCH

Transcript

1. MS EXCEL CA MRIDUL BANSAL 13/08/2017 1
2. TOPICS • VLOOKUP……………………………………………………………………… • HLOOKUP……………………………………………………………………… • INDEX MATCH.................................................. • INDEX MATCH V/S VLOOKUP/HLOOKUP………………………… 3-7 17-20 13-16 8-12 13/08/2017 2
3. VLOOKUP VLOOKUP LOOKS FOR A VALUE IN THE LEFTMOST COLUMN OF A TABLE THEN RETURNS A VALUE IN THE SAME ROW FROM A COLUMN YOU SPECIFY VLOOKUP FUNCTION IS USED TO SEARCH A VALUE IN A TABLE IF FOUND, RETURN THE CORRESPONDING ROW VALUE OF THAT TABLE FOR THE SPECIFIED COLUMN IN OTHER WORDS 13/08/2017 3
4. =VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP]) VALUE YOU WANT TO LOOK UP THE RANGE WHERE THE LOOKUP VALUE IS LOCATED COLUMN NUMBER IN THE RANGE THAT CONTAINS THE RETURN VALUE OPTIONALLY, YOU CAN SPECIFY ‘TRUE’ FOR AN APPROPRIATE MATCH OR ‘FALSE’ FOR AN EXACT MATCH OF THE RETURN VALUE LOOKUP_VALUE TABLE_ARRAY COL_INDEX_NUM RANGE_LOOKUP SYNTAX 13/08/2017 4
5. TYPES RANGE LOOKUP USED WHEN YOU WANT TO SEARCH FOR RANGES, IT WILL LOOK FOR NEAREST MINIMUM VALUE FROM THE FIRST COLUMN OF THE TABLE IT IS AN EXACT MATCH. INDICATED AS FALSE OR 0 EXACT LOOKUP IT IS AN APPROPRIATE MATCH. INDICATED AS TRUE OR 1 USED WHEN YOU WANT TO SEARCH FOR EXACT VALUE IF TABLE CONSIST OF 40, 50, 60, 70 AND YOU SEARCH FOR 55, THEN IT WILL DESTINED TO 50 AS IT IS THE NEAREST MINIMUM VALUE IF TABLE CONSIST OF 40, 50, 60, 70 AND YOU SEARCH FOR 55, THEN IT WILL SHOW NOTHING I.E. NA. 13/08/2017 5
6. EXAMPLE (EXACT LOOKUP) • A2:D12 IS A TABLE OF RESULT OF 10 STUDENTS. • NOW SUPPOSE WE NEED TO FIND WHETHER A PARTICULAR STUDENT IS PASSED OR NOT. • WE USE VLOOKUP FUNCTION AND SEARCH A PARTICULAR ROLL NO. (B14) IN A TABLE (A2:D12) FOR COLUMN NO. 4 FOR RESULT (B15) VLOOKUP FUNCTION SEARCH FOR ROLL NO. 100245 IN A TABLE, AND SHOW RESULT IN THE SAME ROW FOR THE COLUMN 4 i.e. FAIL VLOOKUP FUNCTION SEARCH FOR ROLL NO. 100249 IN A TABLE, AND SHOW RESULT IN THE SAME ROW FOR THE COLUMN 4 i.e. PASS • B14 - LOOKUP VALUE • A3:D12 – TABLE ARRAY (SELECT TABLE WITHOUT HEADING) • 4 – COLUMN NO. (FROM THE START) • FALSE – FOR EXACT LOOKUP 13/08/2017 6
7. EXAMPLE (RANGE LOOKUP) • A2:C12 IS A TABLE OF PRICE OF 10 PRODUCTS. • NOW SUPPOSE WE NEED TO FIND THE PRICE OF A PRODUCT FOR A GIVEN QUANTITY • WE USE VLOOKUP FUNCTION AND SEARCH A QUANTITY (B14) IN A TABLE (A2:C12) FOR COLUMN NO. 3 FOR PRICE (B15) VLOOKUP FUNCTION SEARCH FOR NEAREST MIN. QUANTITY TO 75 IN A TABLE, AND SHOW RESULT IN THE SAME ROW FOR THE COLUMN 3 i.e. 48 VLOOKUP FUNCTION SEARCH FOR NEAREST MIN. QUANTITY TO 148 IN A TABLE, AND SHOW RESULT IN THE SAME ROW FOR THE COLUMN 3 i.e. 75 • B14 - LOOKUP VALUE • A3:C12 – TABLE ARRAY (SELECT TABLE WITHOUT HEADING) • 3 – COLUMN NO. (FROM THE START) • TRUE – FOR RANGE LOOKUP 13/08/2017 7
8. HLOOKUP HLOOKUP LOOKS FOR A VALUE IN THE TOP ROW OF A TABLE THEN RETURNS A VALUE IN THE SAME COLUMN FROM A ROW YOU SPECIFY HLOOKUP FUNCTION IS USED TO SEARCH A VALUE IN A TABLE IF FOUND, RETURN THE CORRESPONDING COLUMN VALUE OF THAT TABLE FOR THE SPECIFIED ROW IN OTHER WORDS 13/08/2017 8
9. =HLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, ROW_INDEX_NUM, [RANGE_LOOKUP]) VALUE YOU WANT TO LOOK UP THE RANGE WHERE THE LOOKUP VALUE IS LOCATED ROW NUMBER IN THE RANGE THAT CONTAINS THE RETURN VALUE OPTIONALLY, YOU CAN SPECIFY ‘TRUE’ FOR AN APPROPRIATE MATCH OR ‘FALSE’ FOR AN EXACT MATCH OF THE RETURN VALUE LOOKUP_VALUE TABLE_ARRAY ROW_INDEX_NUM RANGE_LOOKUP SYNTAX 13/08/2017 9
10. • Heading TYPES RANGE LOOKUP USED WHEN YOU WANT TO SEARCH FOR RANGES, IT WILL LOOK FOR NEAREST MINIMUM VALUE FROM THE TOP ROW OF THE TABLE IT IS AN EXACT MATCH. INDICATED AS FALSE OR 0 EXACT LOOKUP IT IS AN APPROPRIATE MATCH. INDICATED AS TRUE OR 1 USED WHEN YOU WANT TO SEARCH FOR EXACT VALUE IF TABLE CONSIST OF 40, 50, 60, 70 AND YOU SEARCH FOR 55, THEN IT WILL DESTINED TO 50 AS IT IS THE NEAREST MINIMUM VALUE IF TABLE CONSIST OF 40, 50, 60, 70 AND YOU SEARCH FOR 55, THEN IT WILL SHOW NOTHING I.E. NA. 13/08/2017 10
11. EXAMPLE (EXACT LOOKUP) • A2:K5 IS A TABLE OF RESULT OF 10 STUDENTS. • NOW SUPPOSE WE NEED TO FIND WHETHER A PARTICULAR STUDENT IS PASSED OR NOT. • WE USE HLOOKUP FUNCTION AND SEARCH A PARTICULAR ROLL NO.(M2) IN A TABLE (A2:K5) FOR ROW NO. 4 FOR RESULT(M3) HLOOKUP FUNCTION SEARCH FOR ROLL NO. 100244 IN A TABLE, AND SHOW RESULT IN THE SAME COLUMN FOR THE ROW 4 i.e. PASS HLOOKUP FUNCTION SEARCH FOR ROLL NO. 100248 IN A TABLE, AND SHOW RESULT IN THE SAME COLUMN FOR THE ROW 4 i.e. FAIL • M2 - LOOKUP VALUE • B2:K5 – TABLE ARRAY (SELECT TABLE WITHOUT HEADING) • 4 – ROW NO. (FROM THE TOP) • 0 – FOR EXACT LOOKUP 13/08/2017 11
12. EXAMPLE (RANGE LOOKUP) • A2:K4 IS A TABLE OF PRICE OF 10 PRODUCTS. • NOW SUPPOSE WE NEED TO FIND THE PRICE OF A PRODUCT FOR A GIVEN QUANTITY. • WE USE HLOOKUP FUNCTION AND SEARCH A QUANTITY(M2) IN A TABLE (A2:K4) FOR ROW NO. 3 FOR RESULT(M3) HLOOKUP FUNCTION SEARCH FOR NEAREST MIN. QUANTITY TO 39 IN A TABLE, AND SHOW RESULT IN THE SAME COLUMN FOR THE ROW 3 i.e. 40 HLOOKUP FUNCTION SEARCH FOR NEAREST MIN. QUANTITY TO 128 IN A TABLE, AND SHOW RESULT IN THE SAME COLUMN FOR THE ROW 3 i.e. 49 • M2 - LOOKUP VALUE • B2:K4 – TABLE ARRAY (SELECT TABLE WITHOUT HEADING) • 3 – ROW NO. (FROM THE TOP) • 1 – FOR RANGE LOOKUP 13/08/2017 12
13. INDEX MATCH INDEX MATCH IS A COMBINATION OF TWO FUNCTIONS INDEX MATCH RETURNS A VALUE OR REFERENCE OF THE CELL AT THE INTERSECTION OF A PARTICULAR ROW AND COLUMN, IN A GIVEN RANGE. RETURNS THE RELATIVE POSITION OF AN ITEM IN AN ARRAY THAT MATCHES A SPECIFIED VALUE IN A SPECIFIED ORDER. WHEN WE USE INDEX AND MATCH FUNCTION TOGETHER, THE MATCH FUNCTION FINDS THE LOOKUP_VALUE’S ROW INDEX OR COLUMN INDEX NUMBER AND THEN PASSES THE INFORMATION TO THE INDEX FUNCTION, WHICH RETURNS THE INFORMATION WHICH WE ACTUALLY WANT. 13/08/2017 13
14. INDEX =INDEX(ARRAY, ROW_NUM, [COLUMN_NUM]) SYNTAX ARRAY : A RANGE OF CELLS ROW_NUM : ROW IN AN ARRAY FROM WHICH TO RETURN A VALUE COLUMN_NUM : COLUMN IN AN ARRAY FROM WHICH TO RETURN A VALUE THIS RESULTS VALUE AT THE INTERSECTION OF ROW 3 AND COLUMN 2 IN THE ARRAY A3:B12 THIS RESULTS VALUE AT THE INTERSECTION OF ROW 5 AND COLUMN 1 IN THE ARRAY A3:B12 THIS RESULTS VALUE AT THE INTERSECTION OF ROW 9 AND COLUMN 1 IN THE ARRAY A3:B12 13/08/2017 14
15. MATCH =MATCH(LOOKUP_VALUE, LOOKUP_ARRAY, [MATCH_TYPE]) SYNTAX LOOKUP_VALUE : VALUE YOU USE TO FIND A VALUE YOU WANT IN A TABLE LOOKUP_ARRAY : A RANGE OF CELLS IN WHICH TO LOOK FOR VALUES MATCH_TYPE : THE NUMBER 0 FOR EACT MATCH & 1,-1 FOR APPROX MATCH THIS LOOKUP FOR “COATS” IN THE RANGE A3:A12 AND RETURN ITS POSITION THIS LOOKUP FOR “JEANS” IN THE RANGE A3:A12 AND RETURN ITS POSITION THIS LOOKUP FOR “SHOES” IN THE RANGE A3:A12 AND RETURN ITS POSITION 13/08/2017 15
16. INDEX MATCH A2:D12 IS A TABLE OF RESULT OF 10 STUDENTS. NOW SUPPOSE WE NEED TO FIND WHETHER A PARTICULAR STUDENT IS PASSED OR NOT. WE USE INDEX MATCH AND SEARCH A PARTICULAR ROLL NO. (B14) IN A TABLE (A2:D12) MATCH FUNCTION SEARCH FOR ROLL NO. 100245 IN AN ARRAY A3:A12 AND RETURN ITS POSITION i.e. IT RETURNS 5. THEN INDEX FUNCTION RETURNS A VALUE OR REFERENCE OF THE CELL IN THE 5TH ROW OF AN ARRAY D3:D12 (RESULTS). IT RETURNS REFERENCE OF CELL D5 i.e. FAIL • B14 - LOOKUP VALUE • A3:A12 – LOOKUP ARRAY • 0– FOR EXACT LOOKUP • D3:D12 – RESULT ARRAY MATCH FUNCTION SEARCH FOR ROLL NO. 100249 IN AN ARRAY A3:A12 AND RETURN ITS POSITION i.e. IT RETURNS 9. THEN INDEX FUNCTION RETURNS A VALUE OR REFERENCE OF THE CELL IN THE 9TH ROW OF AN ARRAY D3:D12 (RESULTS). IT RETURNS REFERENCE OF CELL D9 i.e. PASS 13/08/2017 16
17. INDEX MATCH V/S VLOOKUP/HLOOKUP INDEX MATCH RETURNS A REFERNCE RATHER THAN A VALUE, WHICH ALLOW US TO DO MORE THINGS IN ONE FORMULA INDEX MATCH CAN LOOKUP EITHER VERTICAL OR HORIZONTAL DATA, WITH NO NEED TO CHANGE FUNCTIONS INDEX MATCH CAN RETURN APPROXIMATE MATCHES FROM DATA SORTED FROM LARGEST TO SMALLEST. WE CAN’T DO SAME WITH LOOKUP FUNCTION AT IT WORST, INDEX MATCH IS SLIGHTLY FASTER THAN VLOOKUP AND AT ITS BEST, IT IS MANY TIMES FASTER THERE CAN BE TWO REASONS YOU USE VLOOKUP/HLOOKUP INSTEAD OF INDEX MATCH. EITHER YOU DON’T KNOW HOW TO USE INDEX MATCH OR YOU KNOW NOTHING ABOUT INDEX MATCH 1 3 4 5 2 13/08/2017 17
18. EXAMPLE 1 IN VLOOKUP FORMULA LEFTMOST COLUMN MUST BE THE LOOKUP RANGE AND VALUE RANGES TO BE ON THE RIGHT. BUT INDEX MATCH HAS NO SUCH LIMITATION. THE LOOKUP RANGE IS TO THE RIGHT OF THE VALUE RANGE MATCH FORMULA SEARCH FOR PRODUCT WE ENTER IN CELL B9 IN THE ARRAY B3:B7 AND RETURN ITS POSITION. THEN IT PASSES INFORMATION TO INDEX FORMULA WHICH RETURN THE REFERENCE OF CELL AT THE SAME POSITION FOR THE GIVEN RANGE i.e. FOR RANGE A3:A7 13/08/2017 18
19. EXAMPLE 2 WE CAN’T MIX HORIZONTAL AND VERTICAL LOOKUPS i.e. VLOOKUP AND HLOOKUP CAN’T BE MIX TOGETHER. BUT INDEX MATCH HAS NO SUCH LIMITATION. THE LOOKUP RANGE IS HORIZONTAL AND THE VALUE RANGE IS VERTICAL MATCH FORMULA SEARCH FOR PRODUCT WE ENTER IN CELL D4 IN THE ARRAY B2:F2 AND RETURN ITS POSITION. THEN IT PASSES INFORMATION TO INDEX FORMULA WHICH RETURN THE REFERENCE OF CELL AT THE SAME POSITION FOR THE GIVEN RANGE i.e. FOR RANGE A5:A9 13/08/2017 19
20. EXAMPLE 3 AS EXPLAINED IN EXAMPLE 2 WE CAN’T MIX HORIZONTAL AND VERTICAL LOOKUPS IN ONE FORMULA TO RETURN VALUES FROM A COMBINATION OF VERTICAL AND HORIZONTAL LISTS. BUT INDEX MATCH HAS NO SUCH LIMITATION. THE LOOKUP RANGE IS VERTICAL AND THE VALUE RANGE IS HORIZONTAL MATCH FORMULA SEARCH FOR PRODUCT WE ENTER IN CELL D4 IN THE ARRAY A5:A9 AND RETURN ITS POSITION. THEN IT PASSES INFORMATION TO INDEX FORMULA WHICH RETURN THE REFERENCE OF CELL AT THE SAME POSITION FOR THE GIVEN RANGE i.e. FOR RANGE B2:F2 13/08/2017 20