I did a post not too long ago on the vlookup function and how it can be used to extract data by matching on a row and returning data x number of columns away. The same exists using the hlookup command to match on a column and return data x number of rows away.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The use case for this is simple: you’re doing a parallel upgrade of Call Manager using the import/export method and you want to copy over all the tables that exist in the old database, but if they don’t exist, you still need the table headers. To do this you’ll need the old phone.csv and a blank export of the new phone.csv side by side.

You’ll paste this on the new phone.csv in A2 and drag to the right and down to populate the database.

=hlookup(a$1,(old-phone!$a$1:$dyt$5000),(row(a2)),false)

When you’re done you should copy all values to a new sheet to clean up any #N/A values it may create and thats your new phone.csv

Leave a Reply

Post Navigation