Lookup/Match Formula

[MROMAR]

I need a formula that looks up a certain value in a range and return a value in the corresponding row.

In Sheet 2 A1 I have VAN, in Sheet 1 row B I have varies cites, VAN, TOR, MTL, SAS.

The formula would need to look up any VAN Sheet 1 Row B and return the value in Row E.

For example if in Sheet 2 B4 I have VAN, then I need to return E4 to Sheet 2.

Now there are going to be many VAN's which is why a Vlookup wouldn't work, is there a way to return all the Column E cells that have a have VAN in Column B.

So on Sheet 2 it would look similar to this

VAN

1VANCLG1
1VANCLG2
1VANCLG3
1VANCLG4
1VANEDM1
1VANKEL1
1VANMTL1
1VANMTL1
1VANSAS1
1VANSAS2
1VANTOR1
1VANTOR2
1VANWPG1

So the formula would need to be in Sheet 2 Cells A2-A50

[Haseeb Avarakkan]

Try this in Sheet2!A2, with CTRL+SHIFT+ENTER, not just ENTER

=IFERROR(INDEX(Sheet1!E:E,SMALL(IF(Sheet1!C$2:C$100=A$1,ROW(Sheet1!C$2:C$100)),ROWS(A$2:A2))),"")

Adjust the range. Then copy A2 & fill down.

[MROMAR]

That didn't really work, all it did was return the first value in Sheet 1 column E.

[Haseeb Avarakkan]

Did you confirm the formula with CTRL+SHIFT+ENTER?

[MROMAR]

What does tha do? I pressed CTRL+SHIFT+ENTER and nothing happened

[MROMAR]

So I've attached a file.

Row B is the lookup value,and Row E is the return value that I need.

So in Sheet 2 there should be 81 entries

[Haseeb Avarakkan]

In Sheet1 'Origin' is on column B, formula was looking on column C, so change C$2:C$100 to B$2:B$100

=IFERROR(INDEX(Sheet1!E:E,SMALL(IF(Sheet1!B$2:B$100=A$1,ROW(Sheet1!B$2:C$100)),ROWS(A$2:A2))),"")

Also, adjust the length.

Here is a link about Array Formulas.

http://www.databison.com/index.php/e...ray-constants/

[MROMAR]

I've done the changed you suggested and this is the result I end up with

Book2.xlsx

[MROMAR]

You know what, I finally understood what you meant by CTRL+SHIFT+ENTER, works perfectly, thanks so much!

[Haseeb Avarakkan]

...deleted...