Index Formula Help

Gladston

New Member
Joined
Oct 12, 2014
Messages
10
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!

Brad
 

Caribeiro77

Well-known Member
Joined
Sep 24, 2010
Messages
1,261
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
Joined
Jun 17, 2005
Messages
7,761
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
 

Gladston

New Member
Joined
Oct 12, 2014
Messages
10
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

Well-known Member
Joined
Sep 24, 2010
Messages
1,261
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 ...
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top