Lookup ..... with a twist perhaps?

[heidithecat]

Hi,

I wonder if someone could help me please.....my brain is about to implode.

I've drafted out a very simplified version of what I require (see attached spreadsheet with three tabs).

Basically, I need column E on the "Results" tab to give me the relevant "Acc CC" from the "Table" tab, but this could be different, depending on the combination of information in rows C & D.

I don't know if I've included enough of an explanation here, so please feel free to shout at me if necessary

Many thanks in advance,

HtC

[FDibbins]

Try this, copied down...
=INDEX(Table!$C$3:$E$12,MATCH($C2,Table!$A$3:$A$12,0),MATCH($D2,Table!$C$2:$E$2,0))

[heidithecat]

I can't thank you enough! It works perfectly! .......

I will now go away and study that formula carefully, just to see if I can finally try to understand Index Match!

Thank you!!

[FDibbins]

To give you a nudge in the right direction...
Dont consider INDEX/MATCH as 1 formula, it is actually 2 (or 3 in this case) seperate functions

INDEX() syntax is =index(criteria-to-find,row-to-search-in,column to search in)
so something like =INDEX(A1:J10,3,5)
will return the contents of E3 (row 3, column 5)
Didnt even use MATCH, did we?

Now, to find the "3" or the "5", we would use the MATCH function...
MATCH syntax is...=match(criteria-to-find,column-to-search-in,0) 0 returns exact match
=MATCH("cc",A1"A10,0)
If "cc" is in A3, this will return 3
(to find the column number, we use the same thing, just change the range=MATCH(criteria-to-find,row-to-search-in,0)
=Match("zz",A1:J1,0)
If "zz" is in E1, then this will return 5

Put them all together and you have...
=INDEX(A1:J10,MATCH("cc",A1:A10,0),MATCH("zz",A1:J10))

Does that make sense?

[heidithecat]

It certainly looks like something I can practice with, to try to understand it better. I shall give it a try in the morning hopefully. It's almost 1am here now, so I ought to shut down and get some zz myself!

Many thanks once again..... I love excel (workmates think I'm potty!), but have so much to learn!

[FDibbins]

Happy to help, and welcome to the potty brigade lol