One method uses VLOOKUP and direct worksheet and cell references. The other approach uses INDEX & MATCH and Excel Table names and references. The key here is that the INDIRECT function acts as the messenger that returns the correct sheet address in a dynamic way to the different lookup formulas. The limitation of the VLOOKUP function is only lookup for one condition, that is lookup_value. If there are two or more criteria the easiest way is to concatenate all the criteria in the lookup_value. The next step is to create the helper column that contains a merge of two columns in table_array.

Jan 10, 2019 · Syntax of VLookup merge data from one sheet to another Excel worksheet: The Microsoft Excel VLookup function search a value in the extreme left column of the table_array and provide the value in the same row which is based on the index_number.