Learn more about using INDEX and MATCH The VLOOKUP function can only return one record.
It will return the first record that matches the value you looked for. A quick way to do this is to select the table and click the Removes Duplicates button on the Data tab. A Pivot Table would be perfect to select a value and list the results instead. Let's say you want to return all the orders for a particular fruit.
The table that the VLOOKUP function uses to look for and return information from is known as the table_array.
This will need to be referenced absolutely to copy your VLOOKUP.
This makes the col_index_num dynamic so inserted columns will no longer affect the VLOOKUP.The formula below could be entered in this example to prevent the problem demonstrated above.As more rows are added to the table, the VLOOKUP may need to be updated to ensure that these extra rows are included.If looking for a unique value, enter FALSE for the last argument. Maybe you are looking to use multiple VLOOKUPs to return different information about a record.If you are planning to copy your VLOOKUP to multiple cells, you will need to lock your table.Because this is entered as an index number, it is not very durable.If a new column is inserted into the table, it could stop your VLOOKUP from working. The quantity was in column 3, but after a new column was inserted it became column 4. One solution might be to protect the worksheet so that users cannot insert columns.Click on the references within the formula and press the .In this example both the lookup_value and table_array references were made absolute.If users will need to be able to do this, then it is not a viable solution.Another option would be to insert the MATCH function into the col_index_num argument of VLOOKUP.