Excel 2007 : VLookup with free key option?

**bjohnsonac** · 09-21-2010, 11:00 AM

Is there a way to use the Vlookup formula also giving the user the opportunity to hard key data? I have a sheet with drop-down list in column A and the Vlookup (for price) in column B; however, they need to be able to hard key data into col A (if product not in the drop-down provided) and then subsequently hard key the price into Col B....there is bound to be VBA code I can use instead of the vlookup formula directly in the cells in column B

**Marcol** · 09-21-2010, 11:21 AM

You could try this, or some variation of the principle.

=IF(ISERROR(VLOOKUP(First_Ref,LookupList,Column,FALSE)),VLOOKUP(Alt_Ref,LookupList,Column,FALSE),VLOOKUP(First_Ref,LookupList,Column,FALSE))

Where First_Ref is your validation list with no value entered, and Alt_Ref is the cell to type your missing entry.

Why is there missing entries in your validation list?
It would be better to fix that, than guess at some missing piece of data. No?

Hope this helps

**Marcol** · 09-21-2010, 12:47 PM

That last post was not very clear.

This example workbook might help to explain better.

**bjohnsonac** · 09-21-2010, 01:01 PM

I am going to attach a sample to try to explain what I need more clearly. The user will select the product from the validation list in column A and that products price pops into column B.. However, if they need a product not listed in the validation list in column A, they need to be able to type the new product name in column A as well as type the price into column B - I don't want to delete the vlookup option when they type tho - I was hoping there was VBA code for a macro that could be used instead of the vlookup formula in the cells.