Self-contained VLOOKUP

Formula

=VLOOKUP(lookup,{table_array},column,match)

Explanation

The second argument is normally expressed as a table, for example, B3:G6. However, you must have the table present in the same sheet to be able to use it; otherwise, you can code the table into an array constant. VLOOKUP can evaluate both as the same thing.

Example

Figure 1. Self-contained VLOOKUP

In the example, the left table, excluding its headers, is coded as an array constant with the following code, which is equivalent to B5:C8

{"B18","D";"C65","C";"F88","B";"E16","A"}

Note that the columns are separated by commas and rows are separated by semi-colons.

Tips

Whenever you have a small table, you can write a self-contained VLOOKUP with this approach. The advantage is that you do not have to contain the table in the same sheet and reference in VLOOKUP. On the other hand, the drawbacks are not only that you have to code all the values inside the table, but you also cannot copy the formula into 2 or more cells because of the array constant. Furthermore, editing an array is more challenging than copying a table.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar