vlookup function

[The Needy]

Hi

I have to add the amount of books of the same title from two different
libraries using vlookup because each library has its own amount of that
title, but some books may not be available in those two libraries and I need
to put "Not Available". I am told to use =IF(ISNA(VLOOKUP(*****), 0,
VLOOKUP(*****) ), but idk how to use it in my situation. Please help
me!!!!!!!!!! I need this ASAP!!!!!!!!!! Thank you!!!!!!!!!!

 

[Jacob Skaria]

"two different libraries" means do you mean two different work sheets?

In cell C2 enter book name to be quereied

Sheet1 and Sheet2 contains books and amount in ColA and ColB
respectively...The below formula will add up the amounts in ColB of both
worksheets for the matching book names in ColA...

=SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"}
&"'!A:A"),C2,INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B")))


If this post helps click Yes

 

[The Needy]

Yes, by "two different libraries", I mean two different worksheets. When I
replaced "sheet 1" in your formula, it gave me an error...

 

[Jacob Skaria]

What is the error? Is it REF#? Please note that there are two instances of
sheet names to be changed?

I would suggest you to test this formula in a new workbook with data as
below. The formula should sum up the values for book1....Once you make that
work try replacing the sheet names and the range...

Sheet1
Col A ColB
Book1 10

Sheet2
Col A ColB
Book1 25

If this post helps click Yes

 

[The Needy]

I got =COUNTIF('Store 1 Inventory'!$B$6:$B$36,B8)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B8) to give me the amount of books of the same title
from the two different libraries, but do you know how to make the ones that
are "O" (zero) to "Not Available"?

 

[Jacob Skaria]

To count with 2 conditions use SUMPRODUCT as below. Assume amount is given in
Col C

=SUMPRODUCT(('Store 1 Inventory'!$B$6:$B$36=B8)*('Store 1
Inventory'!$C$6:$C$36>0))

If this post helps click Yes

 

[Jacob Skaria]

Do you mean...

=IF(COUNTIF('Store 1 Inventory'!$B$6:$B$36,B8)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B8)=0,"Not Available",COUNTIF('Store 1
Inventory'!$B$6:$B$36,B8)+COUNTIF('Store 2 Inventory'!$B$7:$B$34,B8))

If this post helps click Yes