VLOOKUP help needed !

[Bitly]

Hello Everyone, First time poster here. Not new to the forums though I have been here quite a few times especially recently and have found the best answers here except this one! I have been unsuccessful at figuring it out and hoping I could get some help here

a background on what im working on would probably help here. I have 2 sheets in a workbook each sheet has the same inventory with different names as they are for 2 different companies that share the same inventory. what I am after is having the inventory on the second sheet update as i change the first sheet. I cant do this cell matches that cell because sheet 1 changes a lot while sheet 2 stays the same. In sheet 1 i have named 2 arrays because i have 2 columns i need to search through for specific products ( products are all jacked up in the 2 columns because its an export with child products ).

hopefully this has been described in detail enough as i am unable to post the workbook due to the workbook being on a computer not connected to the internet and current lack of a thumb drive that has appeared to have grown legs and walk away.

first id like to start with a formula that has been working but is not enough for what i need over all

=IF(ISNA(VLOOKUP("productimsearchingfor",eslist,6,FALSE)=TRUE),"find new",VLOOKUP("productimsearchingfor",eslist2,5,FALSE))

at first i thought this was working i tested and it returned the inventory amount and when i took off a letter to make the product not exsist it returned find new but what i didnt realize at the time was was that the product was in fact in the location eslist2 so when i tested on another product that was in eslist i got errors. so i thought ok this is an easy fix

=IF(ISNA(VLOOKUP("productimsearchingfor",eslist,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("productimsearchingfor",eslist2,5,FALSE)=TRUE),"RE DO THIS",VLOOKUP("productimsearchingfor",eslist,6,FALSE))))

at any rate i do apologize for the long post and hopefully I have made everything clear enough to understand.

Thank you in advance for any help you may be able to provide

 

[Bitly]

i forgot to mention in the end what im looking for is just the above statement but returning something more clever then #n/a if the product cannot be found at all but when im attempting to make that happen the errors start flying

thanks

 

[T. Valko]

i forgot to mention in the end what im looking for is just the above statement but returning something more clever then #n/a if the product cannot be found at all but when im attempting to make that happen the errors start flying

thanks

I'm not sure what you're wanting to do.

Here's my best guess...

First, put the lookup value in a cell:

• A1 = productimsearchingfor

Then:

=IF(COUNTIF(INDEX(eslist,0,1),A1),"It's in eslist",IF(COUNTIF(INDEX(eslist2,0,1),A1),"It's in eslist2","Not Found"))

 

[Aladin Akyurek]

i forgot to mention in the end what im looking for is just the above statement but returning something more clever then #n/a if the product cannot be found at all but when im attempting to make that happen the errors start flying

thanks

If these VLOOKUPs are expected to return a text value, try:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",
     IF(ISNA(VLOOKUP("productimsearchingfor",eslist2,1,0)),"RE DO THIS",""),
      VLOOKUP("productimsearchingfor",eslist,6,0)))

Note that 0 has the same meaning as FALSE. Also, the first VLOOKUP is slightly edited.

 

[Bitly]

well good news is i found my answer before checking back here for reply's...i guess i wasnt good enough at explaining my problem.heh that happens a lot with me...but i do try heh...well the problem was i was just lazy typing and forgetting to cap off the true statement the correct way before starting my false statement so it always looked like to many arguments for an if statment like i was trying to tell it logic,true plus more,false plus hey how ya doing and well excel didnt like my logic at first. then when i peered a bit harder into the code it made more sense to put a few more )) after my true and rearrange my stuff a bit better...heres what ended up working for me which may work as a better understanding to what i was trying and failing to ask for heh.

used this to copy down the list...didnt want that in the answer but needed the next part..i only posted this first part in case people stumble accross the thread and could use it maybe

="=IF(ISNA(VLOOKUP("&O2&",eslist,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("&O2&",eslist2,5,FALSE)=TRUE),"&""""&"NEW"&" "&"VALUE"&""""&",VLOOKUP("&O2&",eslist2,5,FALSE))),VLOOKUP("&O2&",eslist,6,FALSE))"

but this is the test code i ended up with that i implemented into my sheet to do what i was originally looking for

=IF(ISNA(VLOOKUP("testproduct",firstsel,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("testproduct",secondsel,5,FALSE)=TRUE),"find new",VLOOKUP("testproduct",secondsel,5,FALSE))),VLOOKUP("testproduct",firstsel,6,FALSE))

sorry for all the confusion in my post, im not yet a pro poster. but im sure ill happen a long a few more rounds here and hopefully get better...

i thank you guys for reading my horribly long posts and for your reply's it always makes my day when people try to help out even when the poster is half moron ( by no means am i calling anyone in any other thread a moron, only myself )im loving learning excel and still trying to get used to the idea that the simple commands i used for so long can be so elaborate and so MUCH more useful then i had ever imagined.

Thanks again,

long posting mildly retarded me

 

[T. Valko]

well good news is i found my answer before checking back here for reply's...i guess i wasnt good enough at explaining my problem.heh that happens a lot with me...but i do try heh...well the problem was i was just lazy typing and forgetting to cap off the true statement the correct way before starting my false statement so it always looked like to many arguments for an if statment like i was trying to tell it logic,true plus more,false plus hey how ya doing and well excel didnt like my logic at first. then when i peered a bit harder into the code it made more sense to put a few more )) after my true and rearrange my stuff a bit better...heres what ended up working for me which may work as a better understanding to what i was trying and failing to ask for heh.

used this to copy down the list...didnt want that in the answer but needed the next part..i only posted this first part in case people stumble accross the thread and could use it maybe

="=IF(ISNA(VLOOKUP("&O2&",eslist,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("&O2&",eslist2,5,FALSE)=TRUE),"&""""&"NEW"&" "&"VALUE"&""""&",VLOOKUP("&O2&",eslist2,5,FALSE))),VLOOKUP("&O2&",eslist,6,FALSE))"

but this is the test code i ended up with that i implemented into my sheet to do what i was originally looking for

=IF(ISNA(VLOOKUP("testproduct",firstsel,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("testproduct",secondsel,5,FALSE)=TRUE),"find new",VLOOKUP("testproduct",secondsel,5,FALSE))),VLOOKUP("testproduct",firstsel,6,FALSE))

sorry for all the confusion in my post, im not yet a pro poster. but im sure ill happen a long a few more rounds here and hopefully get better...

i thank you guys for reading my horribly long posts and for your reply's it always makes my day when people try to help out even when the poster is half moron ( by no means am i calling anyone in any other thread a moron, only myself )im loving learning excel and still trying to get used to the idea that the simple commands i used for so long can be so elaborate and so MUCH more useful then i had ever imagined.

Thanks again,

long posting mildly retarded me

Glad you got it figured out but to be honest, I'm still not sure what you're trying to do!

If this formula does what you want:

=IF(ISNA(VLOOKUP("testproduct",firstsel,6,FALSE)=TRUE),(IF(ISNA(VLOOKUP("testproduct",secondsel,5,FALSE)=TRUE),"find new",VLOOKUP("testproduct",secondsel,5,FALSE))),VLOOKUP("testproduct",firstsel,6,FALSE))

We can shorten a bit.

You don't have to test for "=TRUE". The ISNA function returns either TRUE or FALSE so testing for TRUE is redundant.

In the VLOOKUP function you can replace the range lookup argument FALSE with 0. This argument will accept TRUE or FALSE but it can also be designated as 1 for TRUE and 0 for FALSE. Saves a couple of keystrokes.

So, here's your formula with those changes:

=IF(ISNA(VLOOKUP("testproduct",firstsel,6,0)),(IF(ISNA(VLOOKUP("testproduct",secondsel,5,0)),"find new",VLOOKUP("testproduct",secondsel,5,0))),VLOOKUP("testproduct",firstsel,6,0))

You can reduce that further by using a cell to hold the lookup value:

=IF(ISNA(VLOOKUP(A1,firstsel,6,0)),IF(ISNA(VLOOKUP(A1,secondsel,5,0)),"find new",VLOOKUP(A1,secondsel,5,0)),VLOOKUP(A1,firstsel,6,0))

 

[Bitly]

thank you for the ideas on shortening, I hadn't even thought of that. the idea of what i was doing worked but didnt end up getting used by the end user.. not by fault of me or anything but he took a totally different approach the idea was that i have 2 friends whom both own the same types of companies, which in fact they share the same inventory. one wholesales from the other. they use 2 totally different types of managing there inventory which is managed with an online data base. so the scenario goes as such to keep inventory. friend 1 exports his inventory in xls form sends to friend 2. friend 2 takes that xls and creates another sheet with his own export so now we have 2 sheet in 1 workbook with an export of both inventories from both friends. now friend 2 goes through each and every product comparing both sheets searching in the ever changing inventory of friend 1 and is putting in each inventory cell of his own a very basic formula...this cell on my inventory equals this cell on his inventory. now this would have been fine except friend 1's export changes a lot and has around 1000 products. so each and every week friend 2 is going through the whole thing doing this cell equals this cell. so i wanted to help by taking his inventory and instead of this cell equals this cell putting in a formula that would search friend 1's products for friend 2's to auto update and stop all the this cell equals this cell madness which consumed nearly 3 - 4 hours per week. and friend 1 you have to look basically all the way down column a and b to find the correct product as "a" contains parent products a lot of which do not have children and a lot that do. the children are held in column "b" . also to note friend 2's sheet's product listing never changes because his export and software is more sophisticated. so i needed each inventory cell on his page to search and find the product on friend 1's inventory listing ( column a and b ) and then report the exact match back to his own inventory so now both inventories are sync'd . its a lot and pretty confusing sometimes and there are simpler approaches via database managment stuffs..but this is the way they are handling it right now and i was just throwing in ideas...friend 2 got tired of updating all the time and has no chosen a path of keeping only 5 - 10 products in his inventory so that even when friend 1 has 100 of that product hes only accounting for 5 - 10 so he knows he can cover what he sells rather then try to keep up with the extra hours of inventory calculation and possible back orders. there are probably more simple ways to do such tasks and id still appreciate to learn them. i love excel and actually delt with excel for a few years while in the military as an HR person.always was able to come up with a solution for what i needed at the time but they were rather simple. now that im getting back into excel im finding so many more things i can do with these formulas...i havent even tried playing with match or index or things of that nature but im sure there are tons of uses...its hard for me to fathom countif in my situation but ive seen it suggested in other forums via other posts. because of my limited use of countif..i also have another project im working with and have finished to my liking so far. its just a copy of 2 yahtzee cards which me and my daughter play a lot of and run out of cards a lot so i thought it would be neat to just go green with it and do it on the laptops...so currently im trying to figure out how she can have a laptop and me have a laptop and we both update the cards as we roll and we can see the others updates in real time would be awsome..but then i also want a button i guess with a macro to save certain variables when the game is finished that i can press and it would keep up with who did what on what day as far as scores..would kind of mimic us keeping our old cards plus i could also take that data and have a little fun with that as well in a years time i could be like...yeah this year i kicked your butt by 1 mil points how bout that....but yeah those are the 2 things im working on now haha. but i have had absolutely no luck this far and my vb skills are in the negative :/
at any rate love the replys and again sorry for the long posting

thanks for reading !

Derek

 

[T. Valko]

thank you for the ideas on shortening, I hadn't even thought of that. the idea of what i was doing worked but didnt end up getting used by the end user.. not by fault of me or anything but he took a totally different approach the idea was that i have 2 friends whom both own the same types of companies, which in fact they share the same inventory. one wholesales from the other. they use 2 totally different types of managing there inventory which is managed with an online data base. so the scenario goes as such to keep inventory. friend 1 exports his inventory in xls form sends to friend 2. friend 2 takes that xls and creates another sheet with his own export so now we have 2 sheet in 1 workbook with an export of both inventories from both friends. now friend 2 goes through each and every product comparing both sheets searching in the ever changing inventory of friend 1 and is putting in each inventory cell of his own a very basic formula...this cell on my inventory equals this cell on his inventory. now this would have been fine except friend 1's export changes a lot and has around 1000 products. so each and every week friend 2 is going through the whole thing doing this cell equals this cell. so i wanted to help by taking his inventory and instead of this cell equals this cell putting in a formula that would search friend 1's products for friend 2's to auto update and stop all the this cell equals this cell madness which consumed nearly 3 - 4 hours per week. and friend 1 you have to look basically all the way down column a and b to find the correct product as "a" contains parent products a lot of which do not have children and a lot that do. the children are held in column "b" . also to note friend 2's sheet's product listing never changes because his export and software is more sophisticated. so i needed each inventory cell on his page to search and find the product on friend 1's inventory listing ( column a and b ) and then report the exact match back to his own inventory so now both inventories are sync'd . its a lot and pretty confusing sometimes and there are simpler approaches via database managment stuffs..but this is the way they are handling it right now and i was just throwing in ideas...friend 2 got tired of updating all the time and has no chosen a path of keeping only 5 - 10 products in his inventory so that even when friend 1 has 100 of that product hes only accounting for 5 - 10 so he knows he can cover what he sells rather then try to keep up with the extra hours of inventory calculation and possible back orders. there are probably more simple ways to do such tasks and id still appreciate to learn them. i love excel and actually delt with excel for a few years while in the military as an HR person.always was able to come up with a solution for what i needed at the time but they were rather simple. now that im getting back into excel im finding so many more things i can do with these formulas...i havent even tried playing with match or index or things of that nature but im sure there are tons of uses...its hard for me to fathom countif in my situation but ive seen it suggested in other forums via other posts. because of my limited use of countif..i also have another project im working with and have finished to my liking so far. its just a copy of 2 yahtzee cards which me and my daughter play a lot of and run out of cards a lot so i thought it would be neat to just go green with it and do it on the laptops...so currently im trying to figure out how she can have a laptop and me have a laptop and we both update the cards as we roll and we can see the others updates in real time would be awsome..but then i also want a button i guess with a macro to save certain variables when the game is finished that i can press and it would keep up with who did what on what day as far as scores..would kind of mimic us keeping our old cards plus i could also take that data and have a little fun with that as well in a years time i could be like...yeah this year i kicked your butt by 1 mil points how bout that....but yeah those are the 2 things im working on now haha. but i have had absolutely no luck this far and my vb skills are in the negative :/
at any rate love the replys and again sorry for the long posting

thanks for reading !

Derek

Ok, good luck! Thanks for the feedback!