Is this an Index/Match formula?



Hello -

I have 2 tabs of data.
The first tab has info by product and its qualifying vendors and
non-qualifying vendors (the vendors are in seperate columns)
ColumnA ColumnB ColumnsC ColumnD ColumnE
Product X Vendor1 Vendor2 Vendor3 Vendor4

Column A will always be unique
Columns B and C are qualifying vendors and Column D and E are non-qualifying.

In the second tab I have the following:
ColumnA ColumnB
Product Vendor

What formula do I use to tell me if the vendor is Qualifying (Q) or
Non-Qualifying (N) or blank if the vendor is not included in the list.

Any help would be greatly appreciated!

Thank you in advance.

Paul C

Use the Offset function to establish your range like this (I assume you would
have some kind of header in Row 1

Sheet 1
Row 1 ColumnA ColumnB ColumnsC ColumnD ColumnE
Row 2 Product X Vendor1 Vendor2 Vendor3 Vendor4

Sheet 2
Row 1 ColumnA ColumnB
Row 2 Product Vendor

This establishes a range starting match(A2,Sheet1!$A$2:$A$10,0) rows from A1
and 1 column over. The range is 1 row in height and 4 columns in width)
then use this for your match


Finally set your Qualifying conditions with an IF


This formula goes in C2 on Sheet 2

Bob Phillips

Try this



Thank you both Paul and Bob - both formulas worked perfectly!
Thanks for the quick response as well.

Paul C

Just a quick note

Bob's method is the better of the two, Offset is a volitile function and can
bog down large sheets with calculations.

I did not even think to use SUMPRODUCT with a condition across two columns.
I use conditional SUMPRODUCT all the time, but my conditions are limited to
individual columns. A very useful trick to remember. I can go home now, I
learned something today.

Bob Phillips


A point to note if you use multiple columns in the range being tested in
SUMPRODUCT, don't use the double unary form
(--(rng1=condition2),--(-rng2=condition2)), use the multiplication operator.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question