vlookup function
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!!!!!!!!!! |
vlookup function
"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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
vlookup function
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" wrote: "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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
vlookup function
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 --------------- Jacob Skaria "The Needy" wrote: 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" wrote: "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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
vlookup function
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" wrote: 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 --------------- Jacob Skaria "The Needy" wrote: 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" wrote: "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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
vlookup function
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$360)) If this post helps click Yes --------------- Jacob Skaria "The Needy" wrote: 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" wrote: 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 --------------- Jacob Skaria "The Needy" wrote: 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" wrote: "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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
vlookup function
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 --------------- Jacob Skaria "The Needy" wrote: 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" wrote: 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 --------------- Jacob Skaria "The Needy" wrote: 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" wrote: "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 --------------- Jacob Skaria "The Needy" wrote: 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!!!!!!!!!! |
All times are GMT +1. The time now is 01:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com