Thread: Complex IF, MATCH & INDEX functions

Hi,

I'm new to this forum in particular, but quite a regular on other forums....

I got this problem and I cant seem to find a solution....

A have a set of table of information...

ie: to/from a b c d e
a 0 1 2 3 4
b 1 0 2 6 6
c 2 2 0 7 5
d 3 6 2 0 1

what would be the function to make something go,

to| from| value
a | b| ?

The values under to & from are a data list. What I want to do is get a function that will get the value that corresponds to that one. For example,

to| from| value
a | b| 1

while keeping it dynamic, so if I change the "to" value to become "d", the value under "value" will change.

Thanks for any help! This has been quite a challenge for me...as I've tried all sorts.
Here's some of my failed formulas:

IF(A8=A44:A51,INDEX('Carbon for flight'!A5:A12,MATCH(B8=A44:A51,'Carbon for flight'!A4:I12)))

(IF(Sheet1!A:A=A2,IF(Sheet1!B:B=B2,IF(Sheet1!C:C>C2,IF(Sheet1!C:C<D2,Sheet1!D:D,0)))))

IF(A8=A44:A51,INDEX('Carbon for flight'!A4:A11,MATCH(B8=A44:A51,'Carbon for flight'!A3:I11)))

I just cant get it to work for me. Am I using the wrong values?

Where's your workbook where we can see this data? Mock up your desired results manually, no formulas, and point them out so we know what you're trying to automate.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

Originally Posted by JBeaucaire

Where's your workbook where we can see this data? Mock up your desired results manually, no formulas, and point them out so we know what you're trying to automate.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

ok..so I've attached a workbook

At the top is the data that I'm using, and from row 17 is kinda what I want to use.

I'm looking for a equation in D18 to search the table for the corresponding value.

Not a complex match at all, it's a standard 2D table, so you use MATCH() functions for both the X and the Y positions in the INDEX.

In D18: =INDEX($B$4:$I$11, MATCH($A18, $A$4:$A$11, 0), MATCH($B18, $B$3:$I$3, 0))

In E18: =D18*C18


Also, there's no need for a separate list for your DV list, it's the same citites as in A4:A11, just use those from your table.

Glad to help.

There's no need to QUOTE my entire posts into your responses, it just clutters up the thread. Use the QUICK REPLY box down below unless you're specifically discussing something I've said further.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.