index match, help please Hello, I am trying to index match a set of data (contai

Question

index match, help please

Hello,

I am trying to index match a set of data (contained in a different worksheet) which looks like this:

On a different work sheet I wanted to display the info like this:

Where the cell which contains "2004", I would let the user type in a year and then the data would self populate the cells matching the data in the first worksheet.

I have tried the usual index match formula, {=index(Range of data,match(1,(xx=xx*(yy=yy),0))} but it doesn't work. I have tried various other methods, such as SumIf statements and various other ways with no luck.

Would anyone know what I should do?

Thanks you !!

Entity program 2001 2002 2003 2004 2005 2006 2007 1 a 123 1234 567 899 1121 1287.1 1453.2 1 b 321 624 5234 566 2083.33333 2054.333 2025.333333 1 c 456 334 565 85 79 -45.5 -170 1 d 654 5632 77 53 -3658.3333 -6447.83 -9237.333333 1 e 7789 5182 76 -354 -4179.3333 -7114.13 -10048.93333 1 f 987 6472.4 -537.9 -655.9 -5813.5667 -9644.6 -13475.62667 1 g 909 7762.8 -1151.8 -957.8 -7447.8 -12175.1 -16902.32 1 h 76 9053.2 -1765.7 -1259.7 -9082.0333 -14705.5 -20329.01333 2 a 54 10343.6 -1561.6 -10716.267 -17236 -23755.70667 2 b 3232 11634 -2379.6 -1863.5 -12350.5 -19766.5 -27182.4 2 c 455 12924.4 -2993.5 -2165.4 -13984.733 -22296.9 -30609.09333 2 d 34 14214.8 -3607.4 -2467.3 -15618.967 -24827.4 -34035.78667 2 e 234 15505.2 -4221.3 -2769.2 -17253.2 -27357.8 -37462.48 2 f 42 16795.6 -4835.2 -3071.1 -18887.433 -29888.3 -40889.17333 2 g -1180.9 18086 -5449.1 -3373 -20521.667 -32418.8 -44315.86667

Explanation / Answer

Hi..

Hi,

Your table in sheet 4, your look-up table in sheet 5, try this one:

=IFERROR(INDEX(Sheet4!$C$2:$I$16, MATCH(Sheet5!B$2&Sheet5!$A3,Sheet4!$A$2:$A$16&Sheet4!$B$2:$B$16,0),   MATCH(Sheet5!$B$1,Sheet4!$C$1:$I$1,0)),"")

To enter as an array formula (CTRL+SHIFT+ENTER)

Year= 2005 1 2 a 1121 -10716.3 b 2083.333 -12350.5 c 79 -13984.7 d -3658.33 -15619 e -4179.33 -17253.2 f -5813.57 -18887.4 g -7447.8 -20521.7 h -9082.03

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.