# Index Formula Help

##### New Member
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:

 Description Company Quantity Unit Eq Grams Price Shipping Total Unit cost Product 1 ABC Store 8 Ounce 226.80 70.72 - 70.72 0.31 Product 2 XYZ Store 1 Gallon 3,628.74 500.00 - 500.00 0.14

<tbody>
</tbody>

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
 Description Company Quantity Unit Eq Grams Price Shipping Total Unit cost Product 1 ABC Store Formula Formula Formula Formula Formula Formula Formula

<tbody>
</tbody>

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

#### Caribeiro77

##### Well-known Member
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

##### Well-known Member
Welcome to the board,

You could go with INDEX & MATCH, but I'd use SUMPRODUCT...
Excel Workbook
ABCDEFGHI
1DescriptionCompanyQuantityUnitEq GramsPriceShippingTotalUnit cost
2Product 1ABC Store
Sheet2

##### New Member
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

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

thanks!

#### Caribeiro77

##### Well-known Member
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 ...

1,082,344
Messages
5,364,803
Members
400,814
Latest member
gangstar67

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...