This makes the col_index_num dynamic so inserted columns will no longer affect the VLOOKUP. The MATCH function can be used to look for and return the required column number. Solution 2Īnother option would be to insert the MATCH function into the col_index_num argument of VLOOKUP. If users will need to be able to do this, then it is not a viable solution. One solution might be to protect the worksheet so that users cannot insert columns. However the VLOOKUP has not automatically updated. The quantity was in column 3, but after a new column was inserted it became column 4. If a new column is inserted into the table, it could stop your VLOOKUP from working. The column index number, or col_index_num, is used by the VLOOKUP function to enter what information to return about a record.īecause this is entered as an index number, it is not very durable. Typically it may be just the table_array that needs locking. In this example both the lookup_value and table_array references were made absolute. The formula should be entered as =VLOOKUP($H$3,$B$3:$F$11,4,FALSE). This will need to be referenced absolutely to copy your VLOOKUP.Ĭlick on the references within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. The table that the VLOOKUP function uses to look for and return information from is known as the table_array. The wrong cell ranges are being referenced for the lookup_value and table array. The image below shows a VLOOKUP entered incorrectly.
If you are planning to copy your VLOOKUP to multiple cells, you will need to lock your table. Maybe you are looking to use multiple VLOOKUPs to return different information about a record. The VLOOKUP above should be entered as =VLOOKUP(H3,B3:F11,2,FALSE). If looking for a unique value, enter FALSE for the last argument. The image below shows a VLOOKUP with the range_lookup argument omitted, and the incorrect value being returned. The TRUE value relies on your data being sorted in ascending order to work. This argument is optional, but if left empty, the TRUE value is used. When looking for a unique value, FALSE should be entered for the range_lookup argument.
In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match. The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match.
0 kommentar(er)
