# Index-Match function with multiple criteria

1. ## Index-Match function with multiple criteria

So I'm trying to create an index/match function that will input data into one sheet referencing the data in another. I want it to input the offer number into the PO sheet for the Purchase order it matches according to: Customer name,Product type, and project \$ amount. The offer number should be inputted in the "Offer #" column. For some reason my index/match formula is returning with a "too many arguments" error. The way I understand the index function and the formula I have been trying is:

=INDEX(Offers!B2:D24,match(Pos!B2,Offers!B2:B24,0,match(Pos!C2,Offers!C2:C24,0,match(Pos!D2,Offers!D2:D24,0),1

I'm sure theres something wrong with the way I'm setting this up but I honestly have no idea what that could be since I've been trying everything. Any help would be greatly appreciated.

2. ## Re: Index-Match function with multiple criteria

Reinstated formula. See posts below.

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Index-Match function with multiple criteria

Hi
The above post is not what you need. I've just realized the combinations of Customer, Product, Amount and Offer are not unique so the above formula is of no value, you need extra criteria applied perhaps the dates for each are unique?

4. ## Re: Index-Match function with multiple criteria

Damn that worked perfectly, Gurus like you make the excel world go around. Thanks a lot DBY-much appreciated.

5. ## Re: Index-Match function with multiple criteria

The dates aren't relevant to matching the data at all, I believe this what you gave me is correct but I'll double check

6. ## Re: Index-Match function with multiple criteria

The Combinations appear more than once with different offer numbers. The formula I gave will only return the first one it finds in the 'Offers Sheet'.

7. ## Re: Index-Match function with multiple criteria

The actual project names/customer names/and amount are unique for the data that I'm using. The sheet I showed you was just a sample therefore the titles were not unique.

8. ## Re: Index-Match function with multiple criteria

That's fine as long as you're happy that it's returning the correct value, all's well. I'll edit my post again and reinsert the formula.

DBY

9. ## Re: Index-Match function with multiple criteria

Returning the first one is fine, I just want a system to show that an offer has been drafted for the Po that has come in. Returning with a bunch of offers would be over complicated for what I'm trying to do, when most of the time two offers wouldn't be created for the same company for the same amount.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1