Data validation and Vlookup issues

**scottwhittaker2333** · 06-03-2010, 09:37 AM

This thread is solved!!I'm not really sure why but every time I use data validation to create a drop down and then vlookup in another cell to change to matching data based on whats in the drop down form the vlookup SearchCriterion it never seems to pull up the correct reference data. As a way around it I have found that if I make sure the dropdown box or the vlookup cell are not selected and then autofilter the SearchCriterion area and sort it assending that it usualy works after that, but I'm pretty sure that I shouldn't have to do that. Is there something wrong with the way that I have the formula? Here is what I have:

Cell R14 contains the dropdown.
Cell Q21 contains this formula: =IF(ISBLANK(R14);"";VLOOKUP(R14;A130:D220;2))
Cell O21 contains this formula "=IF(ISBLANK(R14);"";VLOOKUP(R14;A130:D220;3))

**NBVC** · 06-03-2010, 09:39 AM

probably your Vlookup() needs a fourth argument to determine type of match

e.g. VLOOKUP(R14;A130:D220;2;FALSE)

this looks for an exact match in a not-necessarily sorted list.

**scottwhittaker2333** · 06-03-2010, 09:47 AM

I don't think thats working I tried it and now they return N/A.

**NBVC** · 06-03-2010, 09:50 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**scottwhittaker2333** · 06-04-2010, 11:23 AM

If I did this correctly (my first time attaching a shhet to a forum post) then the attached dummy sheet has the dropdown in light green the vlookups in yellow and if you scroll down a little the vlookup field. it is working correctly for now but only becasue it was autofiltered and sorted.dmys.xls

**NBVC** · 06-04-2010, 12:04 PM

I added the False parameter to each of the Vlookups (there are 3).... and didn't get any #N/A errors after scrambling the data....

Also, the references in cell M10 for the Sum and Count functions can be reduced to F66:Y66 instead of indicating each consecutive cell in those ranges.....

**scottwhittaker2333** · 06-05-2010, 11:41 AM

You are correct, I realized that It didn't work the first time because I was not on my primary computer that has ms office and was trying to do it with open office that I have on my old computer. Sorry! Anyway its working fine now. Thanks!!!