Search for a data in excel sheet
 

Please help me with the formula for the below tables
Shop 1 Shop2
NAME NUMBER OF ITEMS NAME NO OF ITEMS
Apples 40 Banana 15
Banana 25 Guava 20
Grapes 30 Pineapples 13
Orange 50 Orange 27

I need the result in the below format.

NAME NUMBER OF ITEMS
Shop 1 Shop2
Apples 40 #N/A
Banana 25 14
Grapes 30 #N/A
Guava #N/A 20
Orange 50 27
Pineapples #N/A 13

Instead of #N/A, I would like to get 0. Please help me in this regard.
Thanks in Advance

Search for a data in excel sheet
 

Let us assume you have the name, # of items shop1 , name , # of items shop2
in Col A-D of Sheet1 and you have list of names in Col A of sheet2
then enter this in B2 of Sheet2 to get # of items for shop2

=IF(ISNA(VLOOKUP(A2,Sheet1!A:B,2,False)),0,VLOOKUP (A2,Sheet1!A:B,2,False))
and this in C2 to get # of items for shop2
=IF(ISNA(VLOOKUP(A2,Sheet1!C:D,2,False)),0,VLOOKUP (A2,Sheet1!C:D,2,False))

"Sudha" wrote:

Please help me with the formula for the below tables
Shop 1 Shop2
NAME NUMBER OF ITEMS NAME NO OF ITEMS
Apples 40 Banana 15
Banana 25 Guava 20
Grapes 30 Pineapples 13
Orange 50 Orange 27

I need the result in the below format.

NAME NUMBER OF ITEMS
Shop 1 Shop2
Apples 40 #N/A
Banana 25 14
Grapes 30 #N/A
Guava #N/A 20
Orange 50 27
Pineapples #N/A 13

Instead of #N/A, I would like to get 0. Please help me in this regard.
Thanks in Advance