Complicated Lookup Function - Pulling Info From One Sheet To Another
How to use If function with Or & And function.
Videos
***UPDATE: Thank you everyone for the amazing suggestions! It ended up being a much trickier puzzle to master than previously thought and this is the part where I get to say ALL HAIL u/SwngnaMs for figuring it out!
Truly, when I tell you that watching them in action was like watching an extremely skilled martial artist, I mean it. Stunning work - nay, immaculate!
We were able to make it work thanks a few key steps to start (side note: I'm more a visual artist/writer than a masterful spreadsheet developer like some of y'all, so forgive me if I'm not fully explaining things well or using odd metaphors. I'm still in the "I don't know what I don't know" stage of my Excel knowledge", so I'll explain it the best I know how.) -
-
Moving the large export file with all the data to a tab at the end to pull from (additional steps will be taken to have new exports automatically update in that tab so it's as seamless of a process as possible later).
-
Some helper cells were created to do some work on the side to help support the main formulas under the shop headers. A metaphorical flying buttress for this cathedral, if you will.
-
Bolstered by the helper cells, a SUMPRODUCT function was then utilized to pull the exact info we needed from the source data.
So, in summary, the helper cell formulas were as follows in columns X, Y and Z and copied all the way down though the body of the spreadsheet:
=MATCH(A3, Test!A:A, 0)
=AGGREGATE(15, 6, ROW(Test!A:A)/(Test!A:A="Qty available")/(ROW(Test!A:A)>X3), 1)
=AGGREGATE(15, 6, ROW(Test!A:A)/(Test!A:A="")/(ROW(Test!A:A)>Y3), 1)
And the formulas in the cells beneath the shop headers were thus:
=SUMPRODUCT(
(Test!B$1:B$79796=D$1) *
(ROW(Test!A$1:A$79796)>Y3) *
(ROW(Test!A$1:A$79796)<Z3) *
IFERROR(--Test!A$1:A$79796,0)
)
In conclusion - Have you ever wondered what it would be like to build a time machine and travel back Florence in 1504 to see Michelangelo sculpt his statue of David? I don't need to imagine anymore. THANK YOU, u/SwngnaMs!!!
******************
Maybe this isn't as complicated as it seems to be in my head, but essentially I'm trying to pull inventory numbers from one spreadsheet into another. However, the numbers I need to pull in Spreadsheet 1 are listed vertically and I'd like that info to be listed horizontally on Spreadsheet 2.
Here is a quick reference of what I'm trying to achieve. I was able to pull the product names to my second sheet using a VLookup function (woo-hoo!) but trying to pull the inventory numbers by location to Sheet 2 is proving... difficult. Very difficult for me, anyway, especially given how large the spreadsheet is and how the info is formatted. Additionally, some products have additional locations beyond what is listed below - for example a few randomly have a location called "EVENTS", so the Shop Names aren't necessarily consistently placed within the table. So it really needs to be a function that looks up the Qty next to the Shop Name listed under the Item #? Is that even possible?
I've color coded what info I would like to pull and where I need it placed (lighter shades = Info I need pulled, darker shades = shop locations I'm referencing). I hope that makes sense!
SHEET 1 SHEET 2Taking a business analytics course and we are going over the if function. The if function is quite simple and I can use the if function with the “and” or “or” function easily. As I progress through the class, I am being asked to use functions that use an if function with an “or” function within an “and” function or vice versa. Any idea on how to understand these functions a bit better?