Mar 30 2023 03:04 PM
Mar 30 2023 03:04 PM
I have a large report that has 13 columns and 300+ rows that I converted to a table. I'm trying to extract multiple data items from that table to build a new, smaller report. I'm trying to use XLOOKUP and in the formula, I want use the data (ex: Barbara) in the top cell of my new spreadsheet as the name in a named range in the original table, using the XLOOKUP formula below it. Can I use the word "Barbara" in that top cell as the "name of a range" part of a reference? Please help!
Mar 30 2023 05:32 PM - edited Mar 30 2023 08:08 PM
I don't KNOW the answer to your question. I do know what I'd do if I had the question: I'd experiment. Try it out, see if it works. If it does (or, for that matter, if it doesn't) you have your answer.
But I'd also suggest another question for you to consider. Here's what you've written:
I want [to] use the data (ex: Barbara) in the top cell of my new spreadsheet as the name in a named range in the original table, using the XLOOKUP formula below it. Can I use the word "Barbara" in that top cell as the "name of a range" part of a reference?
Unless there's some compelling reason to use that word in both places, which doesn't appear to be the case, I'd advise against it just to avoid confusion, and that would be my advice even if your experiment (you are going to run that test, aren't you?) shows it could work. It's just not a good idea, because formulas would end up referring to column heading and named range with the same word.
UNLESS what you're talking about is using that name (from the header row of the table) in a formula where it's referring to that whole column or some filtered or criteria-based-selection (e.g., via XMATCH), and then you're not using it as you would any other column heading. I am just adding this paragraph to my original answer because it occurred to me that you may have been asking about something that I take for granted... referring to table headings in formulas, which is entirely doable. But don't use the same name for a named range that isn't the same as the column itself; that just would not make sense.
There are plenty of other names around. How about "Babs" for one of those spots?
Mar 30 2023 08:30 PM
Mar 31 2023 08:50 AM
You haven't described the full context here, so this may be wide of the mark, but reading what you've written, I wonder if you're aware of the still relatively new Dynamic Array Functions, UNIQUE, FILTER, SORT.... I've found in many situations that FILTER, in particular, can do a lot of what XLOOKUP and its various _LOOKUP cousins (including INDEX and MATCH, XMATCH) can do, and do those selections more powerfully and neatly.
Here's a great YouTube video that introduces the Dynamic Array Functions to the world. You do need Excel 2021 or newer to use these.
Mar 31 2023 09:37 AM
As Mr. mathetes already mentioned, which I can only agree with.
Please always describe your plans step by step in your concerns, preferably with a file (without sensitive data) or photos. Always state your Excel version, operating system, yes, ask also storage medium, file extension, etc.. This way you can be helped much faster and more precisely.
To get back to your question, as far as I understand, there are two questions... I have attached what I could understand.
First part of the question: A large report has 13 columns and 300+ rows that is convert to a table. How to extract multiple data items from that table to build a new, smaller report ? (short version, as far as I understand it)
You can extract multiple data items from a large table in Excel to build a new, smaller report by using cell references in formulas. A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. In one or several formulas, you can use a cell reference to refer to:
For example, if you have a table with 13 columns and 300+ rows and you want to extract data from column B and rows 5 through 10, you could use the cell reference B5:B10 in your formula. This would tell Excel to look at the data in column B and rows 5 through 10 when calculating the result of the formula. You can also use structured references when working with tables. Structured references are an easy way to refer to data in an Excel table. Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula. For example, if your table is named Table1 and you want to sum the data in column B, you could use the formula =SUM(Table1[Column2]).
Second part of the question: Try to use XLOOKUP in the formula, want use the data in the top cell of the new spreadsheet as the name in a named range in the original table, using the XLOOKUP formula below it. Can I use the word "Barbara" in that top cell as the "name of a range" part of a reference? (short version, as far as I understand it)
You can use the XLOOKUP function to search for a value in a range and return a corresponding value in another range. In your case, you can use the value “Barbara” in the top cell of your new spreadsheet as the lookup value in the XLOOKUP formula.
For example, if “Barbara” is in cell A1 of your new spreadsheet, and you want to search for that value in a named range called “Names” in your original table and return a corresponding value from another named range called “Values”, you could use the following formula:
=XLOOKUP(A1, Names, Values).
This would search for the value “Barbara” in the named range “Names” and return the corresponding value from the named range “Values”.
It’s important to note that the value in the top cell of your new spreadsheet must match one of the values in the named range you are searching.
If it doesn’t, XLOOKUP will return an error.
So much time invested, although it would be much easier if it were a little more concrete.
One should consider that this time is missing with someone else who needs just as much help. Everyone will be helped with it, even those who are not involved in this message. With your permission, here's a tip: Welcome to your Excel discussion space!
Thank you for your understanding and hope to have helped.
...if not please just ignore me.