Show data from cell if it includes a certain number

[Danielpeam]

Hey guys,

Really convoluted question so I've tried to give a simplified example below.

What I'd like to see in Column D is - If the value in C1 appears in A:A please show me the value in Cell B.


A B C D
123 9 745
1234 7 654
12 9 123
3456 8 654
432 6 678
122 7 154
678 5 789

[sweep]

Like this?

=IF(ISNUMBER(MATCH(C1,A:A,0)),B1,"")

[Danielpeam]

That's perfect! Thanks so much!

[Danielpeam]

Sorry I spoke to soon.

So it seems to return whatever is in B1 or equivalent.

What I need it to return is the value in the cell next to the one it matched with.

[Ace_XL]

maybe..in B1 of Sheet2

=IFERROR(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)),"")

based on data in your attachment

Copy down

[ben_hensel]

So you want to take the C value, find a matching value in A-column, and return the same row as the match from B column? That's a simple lookup fuction. Put in D1:

Please Login or Register  to view this content.

Pull down.

If that's not what you need, please articulate what you want the formula to do.

[Danielpeam]

Thanks Ace - this fixed it!


Thanks all for the suggestions!

maybe..in B1 of Sheet2

=IFERROR(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)),"")

based on data in your attachment

Copy down