Using Index/Match

[jallum]

I've hunted for some help in the forum, but I cannot find what I'm looking for or get it to work in Excel 2013. I think some of the threads on Index and Match are the way I should go, but I cannot get it to work.

Basically I have a value on sheet1, and I want to find the first occurrence of it on sheet2 from a range of columns and return a value from a different column within the same row.
For example let's say I'm looking for AAA from sheet1 and columns A:D in sheet 2 looks like this:
AAA BBB CCC 1
DDD AAA EEE 2
FFF GGG HHH 3
AAA JJJ KKK 4
LLL EEE MMM 5

I would expect to get back the value of '1' from column D as first occurence of AAA was found in row 1. If I was looking for KKK, I would expect to get back '4' as KKK was found in row 4.

Any help would be greatly appreciated.

 

[edgarsrom]

=INDEX(Sheet2'D:D,MATCH("AAA",Sheet2'A:A,0))

Or if within Sheet 1 your AAA value is located in A1 then could do: =INDEX(Sheet2'D:D,MATCH(A1,Sheet2'A:A,0))

Is it something you are looking for or I misunderstood what you are trying to achieve?

 

[Excel Planet]

As you are not sure which column you should match against, just wrap if error twice and repeat index and match

 

[jallum]

Sort of. I see your match was only looking at column A. My table wasn't clear enough. Let's assume value I'm looking for is in A1 in sheet1.
Here's the data again. I hope this is clearer...

Row#|ColA|ColB|ColC|ColD
Row1 |AAA|BBB |CCC|1
Row2|DDD |AAA |EEE |2
Row3|FFF |GGG |HHH |3
Row4|AAA |JJJ |KKK |4
Row5|LLL |EEE |MMM |5

I tried your formula with a minor change and it came back #N/A. Formula I had used was
=INDEX(Sheet2!D:D,MATCH(A1,Sheet2!A:C,0))

 

[Marcelo Branco]

Maybe

=INDEX(Sheet2!$D$1:$D$5,MATCH(1,IF(MMULT(--(Sheet2!$A$1:$C$5=A1),{1;1;1})>0,1),0))

M.

 

[jallum]

We have a winner! I have NO idea what you've got here, but I can go back and break it apart function by function to figure it out.
Thanks very much for the help. Greatly appreciated.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

To see what the formula does, step by step, try Formulas > Evaluate formula

M.