Hi all,
I'm trying to come up with a simple way to explain to new users in what situations you would use excel's various lookup formulas, to show why it's important to know what format (cross-sectional vs. panel) your datasets are in -- for example, VLOOKUP can be used to pull values from a cross-sectional dataset, while it cannot be easily used to lookup values in a panel dataset. Here's what I have so far:
Source spreadsheet / Possible lookup formulas
1. cross sectional (1 row per matching variable) / vlookup; index-match; any others?
2. times series or panel (>1 row per matching variable or multiple matching variables) / sumproduct; if-array formulas (countifs, sumifs, averageifs, median-if array); any others?
What am I missing? Is there an easier way to explain this?
Thanks a lot!
I'm trying to come up with a simple way to explain to new users in what situations you would use excel's various lookup formulas, to show why it's important to know what format (cross-sectional vs. panel) your datasets are in -- for example, VLOOKUP can be used to pull values from a cross-sectional dataset, while it cannot be easily used to lookup values in a panel dataset. Here's what I have so far:
Source spreadsheet / Possible lookup formulas
1. cross sectional (1 row per matching variable) / vlookup; index-match; any others?
2. times series or panel (>1 row per matching variable or multiple matching variables) / sumproduct; if-array formulas (countifs, sumifs, averageifs, median-if array); any others?
What am I missing? Is there an easier way to explain this?
Thanks a lot!
