Support Board
Date/Time: Sun, 29 Dec 2024 13:52:39 +0000
Post From: Basic Examples of How You Use INDEX, MATCH / Spreadsheet Performance Improvements Now Available
| [2015-12-18 05:07:46] |
| Sawtooth - Posts: 4143 |
|
I just now tried VLOOKUP in the New Spreadsheets and it doesn't work, but the same formula does in Excel. VLOOKUP is not as flexible/useful as INDEX/MATCH because 1) the table array needs to be in ascending order. 2) the lookup column needs to be to the left of the return column. 3) You can't use it to find, e.g. the max in a range like you can with INDEX/MATCH: =MAX(E3:INDEX(E3:E5,MATCH(1,H3:H5,0),1)) I can't think of a possibility where I'd use VLOOKUP or HLOOKUP in a spreadsheet study. I use INDEX/MATCH in these ways: 1) to return a value from an unsorted column based on a lookup value 2) to find the MAX or MIN in a range 3) to SUM or AVERAGE values in a range 4) using double INDEX/MATCH:INDEX/MATCH to do the same in a dynamic range Here is a link to two links of two examples: http://www.sierrachart.com/supportboard/showthread.php?t=31402&highlight=add I'll post again about the inefficiencies of INDEX/MATCH in the New Spreadsheets after I build some test spreadsheets. |