Vlookup, IF, or something better?

**thesonofdarwin** · 04-14-2010, 08:14 PM

Hello,

I'm trying to figure out what function (or set of) would be best for my task. I need to create a spreadsheet with minimal user-input. Column A will have a list box via data verification and everything else will be locked and filled in through functions by taking data from another sheet. I've created an example of what I'm trying to accomplish and it's attached.

The user will select an option in column A, here "Drinks." Doing so will fill in the data in the columns to the right automatically. My list options in the final document will be fairly extensive, so using IF statements might get pretty crazy. I'm sure there is a more efficient way to handle this. I'm thinking VLookup might be my best route?

Thanks for your time!

**TonyB51** · 04-14-2010, 08:27 PM

Hi,

You could try the following formula in Cell B2 on Input Sheet.

=IF($A2="","",INDEX(data!B$2:B$45,MATCH($A2,data!$A$2:$A$45,0)))

Fill right to column E, then fill column' s B thru E down to how fare you need.

Cheers

Former Darwinite

Tony B

**teylyn** · 04-14-2010, 09:10 PM

Edit TonyB, I had the thread open for a long time and did not refresh before posting. Did not mean to ignore or rehash your post.

Hi,

try this in B2, copy across to E2

=IF(ISBLANK($A2),"",VLOOKUP($A2,data!$A$2:$E$100,COLUMN(B1),FALSE))

Some suggestions:

filling down your data validation in column A all the way to the last row is not such a good idea. You probably want to fill down the formla, too and without one bit of data you'll already end up with a massive file size.

Much better style would be to format your input data as a table. With every new row that is added to a table, the format and formulas will be applied automatically, so there is no need to copy formulas down.

cheers