Index/Match across different sheets

[Blackadar]

This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.

I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:

Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s

Sheet 2
Cell C2 has code 1
Cell E2 needs code 2

I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:

=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)

But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.

 

[martindwilson]

=INDEX(Sheet1!E:E,MATCH(C2,Sheet1!A:A,0))
or
=VLOOKUP(C2,Sheet1!A:E,5,FALSE)

 

[Jonmo1]

Welcome to the board...

here's the basic syntax

=INDEX(ReturnRange,MATCH(LookupValue,SearchRange,0))

According to your explaination..
ReturnRange is 'Sheet 1'!E:E (the range you want to return a value from)
LookupValue is 'Sheet 2'!C2 (the value you want to find a match for)
SearchRange is 'Sheet 1'!A:A (the range to search for the lookupvalue)

So try this in Sheet 2 E2

=INDEX('Sheet 1'!E:E,MATCH('Sheet 2'!C2,'Sheet 1'!A:A,0))

 

[leighnea]

I am trying to do something similar however when I am putting in the formula it is saying... "We found a problem with this formula". The formula I am using is =INDEX(Sheet3!D:D;MATCH(Aging!A2&Aging!C2;Sheet3!G:G&Sheet3!F:F;0)).

 

[mern1]

how would you do this so that it may find multiple matches rather than the first one only?