Jan 04 2021 12:39 PM - edited Jan 04 2021 12:42 PM
Jan 04 2021 01:18 PM
@Jaxtonv This can be done with a variety of functions: XLOOKUP, VLOOKUP, LOOKUP, a combination of INDEX and MATCH, or others. If you provide a sample workbook we can be a little more specific on how to use it, otherwise, just look at the help information about what values need to be passed to it.
Jan 04 2021 02:29 PM
Jan 04 2021 03:13 PM
@Jaxtonv In the attached I ended up using a SUMIFS() formula because there wasn't a single unique column to use for the lookup (i.e. there are 2x Gainsville locations). I also used table notation which might be a little harder for you to read if you aren't familiar with it. And lastly I added the IFERROR() and the 1/1/ to detect if nothing was found BUT if the lookup value is or could be 0 this 'trick' is not good. But then again if when they aren't on list you prefer to assume they are 0 then simply remove the IFERROR() and the 1/1/. Since I don't know all the details of what you need I'm trying to give you options.
Jan 05 2021 03:36 PM - edited Jan 05 2021 03:47 PM
Thank you so much. If I changed the duplicate city names to unique names how would it work in that case.
Also, use table 7 as the reference. How did you determine it was table 7?
Jan 05 2021 03:52 PM - edited Jan 05 2021 03:58 PM
@Jaxtonv I don't know why you would bother at this point since that could be asking for errors (like in the workbook you attached you changed Gainsville to GainsvilleF and GainsvilleG on Sheet2 but not Sheet1. But to answer your question, if the city or another column was guaranteed to be unique then you could simplify the formula slightly. Here is the formula now:
=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY]),"n/a")
but if the CITY was unique then you could use:
=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY]),"n/a")
or you could replace SUMIFS with SUMIF or VLOOKUP or one of the other LOOKUP functions.
Similarly, if you want to assume that if the CITY doesn't exist on the previous list you should use 0 (zero) then it can become:
=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY])
or with the original formula checking city, state, and country so you don't have to tweak the city names, it would be:
=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY])
and as for how I determined it is named Table7 there are a couple ways:
a) click anywhere in the table and on the ribbon click 'Table Design' and then on the left end of the ribbon is shows Table Name
b) when you are creating a formula you can click on a header in the table and it will automatically insert that table column in table reference style
c) if you already have an idea of what the table name might be, you can start to type it while entering a formula and that name will be listed as an option to autocomplete
Jan 05 2021 11:52 PM
Thank you very much it's a huge help