Using data in formulas from different worksheets

[Jaxtonv]

I have two different worksheets that are tables with data that is listed by city. Each worksheet is a separate day. I want to calculate the difference in the two days of revenue. But some pages have a different amount of cities so they don’t necessarily line up to the same row. So New York might occupy the same row as Philadelphia on a different worksheet. Is there a way to match the names in column 1 and using that row of information before calculating the formula?

[#2]

@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.

[#3]

Here is a sample the sample of the spreadsheet. I want to compare the yesterday columns on each spreadsheet but the cities change order so are not in the same row between the worksheets.

[#4]

@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.  

[#5]

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?

[#6]

@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

[#7]

Thank you very much it's a huge help