Search for a data in excel sheet

S

Sudha

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
 
S

Sheeloo

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top