Search Through a Column to Find Match, ignore blanks return Value

**lmc2016** · 05-13-2016, 11:00 AM

Hi All,

This has been confusing me all morning. I have a table of different shops and depending on the order value the order # will be on a different commission tier.

I want to setup a dropdown so I can check the different tiers the shop is on.

VLOOKUP, but that only returns the first match of the shop, so in this instant it is correct for Tier 1, but it won't find Tier 2 or 3 as it returns the match in B:B first.

Index Match If (ignore blank), tried this and it returns Tier 1 but not the other tiers and Index Match has the same result.

ExcelProblem.PNG

Any help would be great. Thanks.

**DBY** · 05-13-2016, 11:24 AM

Hello welcome to the forum.

If there's only one value for each 'Store' in each 'Tier', then you could use SUMIF, for example:

Formula:

Please Login or Register  to view this content.

However, if say 'Bob' occurs in a Tier more than once the values will be summed.

Hope this helps.
DBY

**lmc2016** · 05-13-2016, 11:27 AM

Thanks for the reply.

Yes, there will be multiple instances of the tier for each store, but the tier value would remain the same at 5, 10, 15. But there could be any number of each, I just want to check what that value is for a summary worksheet.

Thanks, LMC

**DBY** · 05-13-2016, 11:39 AM

Ok, I assume then that although there may be many 'Bobs' in a tier the value would always be the same or blank. You could use something like:

Formula:

Please Login or Register  to view this content.

Search Through a Column to Find Match, ignore blanks return Value

LinkBack

Thread Tools

Rate This Thread

Display

Search Through a Column to Find Match, ignore blanks return Value

Re: Search Through a Column to Find Match, ignore blanks return Value

Re: Search Through a Column to Find Match, ignore blanks return Value

Re: Search Through a Column to Find Match, ignore blanks return Value

Thread Information

Users Browsing this Thread

Similar Threads

Formula to return unique value and ignore blanks..

[SOLVED] Search Rows of Data to Find a Match when Match Location Varies by Column and Return Row #

Ignore Blanks for INDEX/MATCH with multiple criteria search

How to ignore blanks in an Index/Match Array Formula

search/match different colum/row and return the value if they match in the new column

[SOLVED] Search Column A and B for string match and return value of column C

Trying to find first and last 15 cells with data within range - how to ignore blanks?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions