Index Formula Help

[Gladston]

Hello Everyone,

I hope all is well.

I am looking for some help with the Index formula. I know that you cannot do a vlookup using multiple criteria, thus the need for an index formula. However, I am 100% new to the index formula, and it honestly confuses me at the moment. Looking to see if someone can help by providing me the formula i need to complete a workbook I am involved in. I am hoping that once I can see the formula in action, used the way i need it to, I can better understand it. With that said, here is what I need help with.

Sheet 1

I have a table on Sheet 1, with the following Data:

[TABLE="width: 848"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Company
[/TD]
[TD]Quantity
[/TD]
[TD]Unit
[/TD]
[TD]Eq Grams
[/TD]
[TD] Price
[/TD]
[TD] Shipping
[/TD]
[TD] Total
[/TD]
[TD]Unit cost
[/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD]ABC Store
[/TD]
[TD="align: right"]8
[/TD]
[TD]Ounce
[/TD]
[TD] 226.80
[/TD]
[TD] 70.72
[/TD]
[TD] -
[/TD]
[TD] 70.72
[/TD]
[TD] 0.31
[/TD]
[/TR]
[TR]
[TD]Product 2
[/TD]
[TD]XYZ Store
[/TD]
[TD="align: right"]1
[/TD]
[TD]Gallon
[/TD]
[TD] 3,628.74
[/TD]
[TD] 500.00
[/TD]
[TD] -
[/TD]
[TD] 500.00
[/TD]
[TD] 0.14
[/TD]
[/TR]
</tbody>[/TABLE]


On Sheet 2, I have essentially the Same Table, but what I am looking to do is make it auomated so that when I type in the Description and Company name, the rest of the information autmatically populates. The idea is that, Sheet 1 has a plethora of possible options, and Sheet 2 is a consolidated list of preferred options. So, if I decide to switch from Product 1 to Product 2, I can have all the relevant and respective data auto update so I don't have to manually do that.


Sheet 2
[TABLE="width: 848"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Company
[/TD]
[TD]Quantity
[/TD]
[TD]Unit
[/TD]
[TD]Eq Grams
[/TD]
[TD] Price
[/TD]
[TD] Shipping
[/TD]
[TD] Total
[/TD]
[TD]Unit cost
[/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD]ABC Store
[/TD]
[TD="align: right"]Formula
[/TD]
[TD]Formula
[/TD]
[TD] Formula
[/TD]
[TD] Formula
[/TD]
[TD]Formula
[/TD]
[TD] Formula
[/TD]
[TD] Formula
[/TD]
[/TR]
</tbody>[/TABLE]


Any and all help would be greatly greatly appreciated. Thanks in advance for your help!

Brad

 

[Caribeiro77]

Hi and welcome to the board....
If both of your tables start in A1, you can paste the following in your sheet 2 cell C2

=INDEX(Sheet1!C2:I3,MATCH(A2&B2,Sheet1!A2:A3&Sheet2!B2:B3,0),0)

and end with CTRL+SHIFT+ENTER since this is an array formula...

 

[njimack]

Welcome to the board,

You could go with INDEX & MATCH, but I'd use SUMPRODUCT...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Description	Company	Quantity	Unit	Eq Grams	Price	Shipping	Total	Unit cost
2	Product 1	ABC Store	8	0	226.8	70.72	0	70.72	0.31
Sheet2

 

[Gladston]

Hello All!

Thanks for the welcome! I have referred to this forum site so often and am finally using it as a member. It's so helpful and you get great information from it. Glad to be here!

Thanks Njimack and Caribeiro77 for your help! I appreciate it. Unfortunate when I tried both methods, i kept getting a 0 returned to me. I modified the sheet names to match my workbook, and entered with CTRL+SHIFT+ENTER, and did receive the brackets. Unfortunately niether one worked. Any idea as to why I would be getting a 0 back?

Getting a better grasp of the formula, I branched out and tried the following

=+INDEX(Table1[[Contact]:[Notes]],AND(MATCH(Table13[[#This Row],[Description]],Table1[Description],0),MATCH(Table13[[#This Row],[Company]],Table1[Company],0)),MATCH(Table13[[#Headers],[Quantity]],'Material Sourcing'!1:1,0))


Now it seems to be pulling data, however, incorrectly. Any advice?

thanks!

Brad

 

[Caribeiro77]

In my formula try this change:

=INDEX(Sheet1!C2:I3,MATCH(A2&B2,Sheet1!A2:A3&Sheet1!B2:B3,0),0)

and end with CTRL+SHIFT+ENTER ...