The key to VLOOKUP MATCH is that we are replacing the “column index number” syntax of VLOOKUP with the MATCH formula. In the example below, the lookup value we’ll be using is the State of “WA” and the lookup array is the orange box surrounding cells B6:F6.
(The match type parameter should be left blank – doing so tells Excel that we want an exact match). To use this formula you’ll need both a lookup value and a lookup array. The match formula returns a position number based on your lookup value’s location within the array you’ve selected. (We’ll address the column index number later and since we are not performing a range lookup, we can leave that part of the syntax blank) In the example below, the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14. To use this formula, you’ll need a lookup value and a table array. =VLOOKUP ( lookup value, table_array, col_index_num, ) The primary formula we’ll be using is VLOOKUP: We’ll look at each of the formulas separately before putting them together. VLOOKUP and MATCH are the two formulas that are combined to perform this lookup. The key difference between using VLOOKUP MATCH versus the basic VLOOKUP formula is that, in addition to your vertical lookup value (what you’ll be looking up down the left side of your table) you’ll also have a column lookup value (what you’ll be looking up across the top of your column headings). VLOOKUP MATCH is actually very similar to VLOOKUP HLOOKUP, but is slightly better because it does not require the creation of an additional row to label your column numbers. This is because VLOOKUP MATCH gives your lookup formula insertion immunity whenever you insert or delete a column within your lookup array, your formula will still pull the correct number.
VLOOKUP MATCH is mainly useful for situations where you intended to perform heavy editing on your data set after you’ve finished writing your formula. Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well. VLOOKUP MATCH is an improved variation of your basic VLOOKUP or INDEX MATCH formula. If you do not, please click here for a beginner’s tutorial on VLOOKUP.
How to use vlookup in excel 2013 for dummies how to#
This tutorial assumes you already have a decent understanding of how to use VLOOKUP. VLOOKUP MATCH is one of several possible lookup formulas within Microsoft Excel.
0 kommentar(er)
