Index Match- Maybe???

I have two columns of data that contain duplicates however they are unique in the sense that the first column sets apart the duplicate in the second. I need to create a formula that looks at the data in column A & B and returns a value from another column from data on another tab. This data would match the criteria of column A&B on the first tab.

Ex: Column A Column B Column on Other Tab
Vendor Number SKU Number Sales $
11048 236872 48
17829 236872 66

I would like to be able to do a formula that would pull in the data from the other tab if it matches columns A&B.

Image.JPG
This is currently what the first tab looks like.
In column F I am trying to place the formula to pull in the value from the other tab. I know that I should be able to do this with an Index Match formula using multiple criterias but I have spent all day trying to get it to work and could really use some help here.

Below is the current formula I am using:{=INDEX('Weekly Sales Raw'!$A$2:$E$1668,MATCH(1,('Weekly Sales Raw'!$A$2:$A$1668=$A3)*('Weekly Sales Raw'!B2:$B$1668=$B3),0),5)}

Thank you, however, that returned an #N/A. Is there anything else I can provide to help?

The formula you quote is an array formula - did you commit it using the key combination of Ctrl-Shift-Enter instead of the usual Enter? If you do this correctly then you should see curly braces { and } around the formula when viewed in the formula bar, but you do not type these yourself.

Hope this helps.

Pete

Thanks Pete! I tried that as well and I still have 0's all down column F.

Well, maybe your values in A3 and/or B3 are text values which look like numbers.

It would help if you attached a sample Excel workbook, so we can check this out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

Let's Try this.

The formula in F3 should be this:

=INDEX('Weekly Sales Raw'!$A$2:$E$9,MATCH(1,('Weekly Sales Raw'!$A$2:$A$9=$A3)*('Weekly Sales Raw'!$B$2:$B$9=$B3),0),5)

You had not anchored the B2 term as an absolute reference. Use Ctrl-Shift-Enter to commit it, then copy down.

Hope this helps.

Pete

Pete - Great catch, however, that doesn't seem to return values either. Any thoughts?

Report

F3=IF($A3<>"",SUMIFS(Table1[Sales $],Table1[Merch Vendor Nbr],Report!A3,Table1[SKU Nbr],Report!B3,Table1[SKU Name],Report!C3),"")

copy down

Originally Posted by BrainDead88

Pete - Great catch, however, that doesn't seem to return values either. Any thoughts?

It worked for me - see attached file.

Pete