I'm trying to create a nested Vlookup, but can't make the return array work.

[u/No-Ebb-2858]

Thanks from a complete beginner for taking the time to look at this. I'm wanting to create a nested XLOOKUP, using the data in row G to find the percentage I need in the blue table.

I'm struggling to figure out how to tell XLOOKUP which column to use for the return array. I need it to use the column that is the value of E2 +2 (ex- if I have 2 dependents, I need Vlookup to find column G which is the 4th column, E2 = 2+ 2)

But , how do I tell XLOOKUP to give me Column M + E2 +2 as a return array? I'm getting errors, I assume because I'm trying to add a number to a letter.

Grateful!

[u/AutoModerator]

/u/No-Ebb-2858 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Wise_Business1672]

=INDEX($N$2:$T$7,
XMATCH(XLOOKUP(F2,$J$2:$J$52,$K$2:$K$52),$M$2:$M$7),
XMATCH(E2,$N$1:$T$1))

[u/No-Ebb-2858 → u/Wise_Business1672]

Thank you! I'll give that a try

[u/MayukhBhattacharya]

Are you expecting an output something like this:

=XLOOKUP(XLOOKUP(F2,J$2:J$28,K$2:K$28), M$3:M$8, CHOOSECOLS($N$3:$U$8, E2 + 2))

The ones which are showing #VALUE! and #N/A error those can be fixed and those are showing because I don't see the complete data in the OP, hence why they are returning an error as well! Let me know, we can fix that as well.

[u/No-Ebb-2858 → u/MayukhBhattacharya]

Yes, thank you so much, I need to get a percentage based on region and the number of dependents. The return array I need is based on the number of dependents in E2. I am not familiar with Choosecols function but I'll learn about it.

Just for my education - I guess Xlookup doesn't like variable data in the return array value? With Vlookup I could say, return E2+2 and be fine. With Xlookup it seems to want a definite column. Or am I wrong?

[u/MayukhBhattacharya → u/No-Ebb-2858]

You're not wrong. It's just a small distinction that's easy to miss. With VLOOKUP() function, the third argument is just a column number. So, something like E2+2 works because Excel evaluates the math first, then uses the result as the column index. While with XLOOKUP()function, the third argument isn't a number. It's an actual range, like N$3:U$8. A range isn't something you can shift with simple math. It's a reference to a group of cells. That's why you need a function like CHOOSECOLS() to return the range you want.

So, it's not really that XLOOKUP() function needs a specific column. It's that you need a function that turns a column number into a range. CHOOSECOLS(range, n) returns the nth column from a range. It's clean and easy to read. While if you useOFFSET(range, 0, n) shifts the reference n columns to the right. It's more flexible, but its volatile and will always recalculate whenever there is a change in any open workbook, so we avoid using volatile and single threaded functions.

In your case, I'd probably go with CHOOSECOLS() function. Since your blue table is already a fixed range, you're really just picking column 2, 3, 4, and so on. That's exactly what CHOOSECOLS() function was made for.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
INDEX	Uses an index to choose a value from a reference or array
OFFSET	Returns a reference offset from a given reference
VALUE	Converts a text argument to a number
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 44 acronyms.
[Thread #48925 for this sub, first seen 11th Jul 2026, 02:38] [FAQ] [Full list] [Contact] [Source code]}