Excel: take horizontal data from table, organize vertically in a different sheet, ignoring blanks


I have a table of vendor contact info organized like:

vendorname | address | city | state | zip | phone | fax | contactperson | phone | email

…and I want to select vendorname from a dropdown menu on a different sheet (got that part handled) which would then populate the contact info for that vendor in the cells below.

To make it trickier, I want it to ignore any blank columns from the table. For example, say there is no fax number for an entry in the table. I don't want it to put an empty cell between 'phone' and 'contactperson.'

Can this be accomplished just using formulas?

Best Answer

You probably want to use the VLOOKUP() function. This would be an example:

=VLOOKUP($B$3, Table1,2,FALSE)

Where $B$3 is the cell with the dropdown and Table1 is the table with contact data. Put this formula in the first cell to be filled in when an item is selected from the dropdown. In the other cells to be filled in, you simply need to increment the third parameter (the column index parameter; eg 2 in the sample above).

AFAIK, there is no way to skip blank columns with this method. You'll probably need VBA for that.

Related Question