Vlookup and Index Match Problem with Data Validation

[Irish15]

Hello,

I've run into a problem using Vlookup and can't seem to find a solution anywhere for my situation. I also tried Match Index and that doesn't work either. Basically, I'm just trying to create a drop down list to quickly view certain values. I'm pretty sure the problem is the formula that is already in the cells I'm trying to retrieve. I used an "If" and "Sum if" function to total up groups (in this case stocks) and leave other cells blank. It looks like the Vlookup and Match Index think the value I want is "0" when the value actually exists. I don't want to change the formula if I don't have to since it finally where I want it.

I attached a sample spreadsheet of what I'm referring to. Below the table in column B I have the drop down I would like to use. I'm trying to get matching values in columns G, I and K. I notice it does work with other columns so that is why I think it is the formula in those cells. I'm hoping someone here knows of a solution or work around for that.

Thanks

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Explain thee NATURE of the problem, not what isn't working.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[Irish15]

I'm trying to use data validation to pull information from 3 columns (G, H, K). That setup is below the table in column B on my sample spreadsheet. Vlookup and Index Match don't work because I have specific formulas in those columns like =IF(B4=$B3,"",SUMIF(B:B,B4,F:F)) which I used to sum up the groups and leave other cells blank. When using the Vlookup function or Match index it says the value is "0" and I'm not sure how to get around that.

Also, I should mention the "blue error arrow" pops up and points to the correct value but still shows 0. Its close to working but not sure what to do from here.

[AliGW]

Thanks - please update the thread title accordingly, then you can get help. Don't forget to update your location, too: a broad indication, such as USA, UK or Europe, will suffice. Thanks.

[AliGW]

That's better - thanks!

[AliGW]

OK - where have you mocked up the expected outcome?

[Irish15]

Sorry about that. I have attached an updated spreadsheet with my expected outcome (highlighted in yellow). Thanks for your help.

[AliGW]

OK - you need to limit the ranges of the formulae within the table, e.g.:

=IF(B11=$B10,"",SUMIF($B$3:$B$13,B11,$F$3:$F$13))

If you don't want to do this, then you will need to move your data out of the way (see attached).

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Irish15]

Wow, didn't realize I just needed to move the location. Thanks so much! This was driving me nuts.

[AliGW]

It's because of the full column references in the table - this caused a circular reference error.

Please mark as solved (see previous post).