Excel & Spreadsheets Prompts

Help me write an Excel formula to look up [what I want to find] from [table/sheet name] where [lookup column] matches [value]. Explain: whether to use VLOOKUP, HLOOKUP, INDEX/MATCH, or XLOOKUP for this situation and why. Write the final formula, explain each argument, and flag common errors to avoid.

Write a dynamic array formula in Excel (using FILTER, SORT, UNIQUE, SEQUENCE, or LAMBDA) to: [describe what you want to achieve — e.g., extract all rows where column B = Active and sort by date]. Explain the formula logic, where to enter it, and the expected output. Also provide a fallback formula for older Excel versions without dynamic arrays.

Build a nested IF or IFS formula for this logic: [describe the conditions and outcomes — e.g., 'If sales > 100k = Gold, if > 50k = Silver, if > 20k = Bronze, else = Unranked']. Write the formula using both IF nesting and the cleaner IFS version. Explain how to handle edge cases and how to add more tiers in future.

Write Excel formulas to solve these date/time problems: (1) Calculate the number of working days between [start date] and [end date] excluding weekends and public holidays, (2) Extract the week number from a date, (3) Find the last day of a given month, (4) Add [X] business days to a date, (5) Calculate age from a date of birth. Show each formula with explanation.

Write Excel SUMIF, SUMIFS, COUNTIFS, and AVERAGEIFS formulas to answer these questions from my data: [describe dataset columns]. Questions: (1) Sum of [column] where [condition], (2) Count of rows where [condition 1] AND [condition 2], (3) Average of [column] where [condition]. Explain how to extend each formula and common mistakes with criteria ranges.