| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!!!!!!!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!!!!!!!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedded If Function in a Vlookup Function | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
| how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
| I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
![The Needy[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
