Excel 2010 Formuls - lookup across sheets

[007dude]

Hello,

I am looking for help with the following formula / lookups

Sheet1 (3 cells one column blank)

1112 house
1114 boat
1116 car
1118 dog
11110 cat

Sheet2

1111 x x x 1111 is NOT on the Table
1112 x x x 1112 Found
1113 x x x 1113 is NOT on the Table
1114 x x x 1114 Found
1115 x x x 1115 is NOT on the Table
1116 x x x 1116 Found
1117 x x x 1117 is NOT on the Table
1118 x x x 1118 Found
1119 x x x 1119 is NOT on the Table
11110 x x x 11110 Found

If there is a match, I want the associated cell in column.

1111 x x x 1111 is NOT on the Table
1112 x x x 1112 house
1113 x x x 1113 is NOT on the Table
1114 x x x 1114 boat
1115 x x x 1115 is NOT on the Table
1116 x x x 1116 car
1117 x x x 1117 is NOT on the Table
1118 x x x 1118 dog
1119 x x x 1119 is NOT on the Table
11110 x x x 11110 cat

My formula

=IF(ISNA(MATCH(A1, Sheet1!$A$1:$A$5, 0)), A1 & " is NOT on the Table", A1 & " Found")

I tried what seemed obvious

=IF(ISNA(MATCH(A1, Sheet1!$A$1:$A$5, 0)), A1 & " is NOT on the Table", sheet1!$c$1)
=IF(ISNA(MATCH(A1, Sheet1!$A$1:$A$5, 0)), A1 & " is NOT on the Table", sheet1!$c1)

Thank you in advance.

 

[SkipVought]

Hi,

Don't understand "3 cells one column blank" in the context of your example.

To get the return value from column C, used the INDEX() & MATCH(). The MATCH() function returns the column offers within the search range (sheet 1, column A)...

=INDEX(Sheet1!$C:$C,MATCH(A1,Sheet1!$A:$A,0),1)

 

[SkipVought]

You might check out this FAQ regarding List and Tables in Excel...

faq68-5184

 

[madonnac]

VLOOKUP will also work for this

=A1 & " " & IFERROR(VLOOKUP(A1,Sheet1!$A$1:$C$55,3,False),"is NOT on the Table")