# Help with cross referencing using XMATCH and INDEX.

The task I'm trying to accomplish is to create an automatic lookup method for conduit sizing. I'm referencing the tables in Annex C of the 2023 NEC, and I've copied one portion into a spreadsheet which I'd like to reference with a function. The goal is to find the minimum conduit size for a given number of conductors of a given diameter. So, for example, if I had 20 #12 conductors, I would first find the #12 row, then read right until I found a number larger than 20, then reference the column header to find that I need a 1" conduit.

From what I've seen in the documentation, I can use XMATCH, INDEX, or maybe XLOOKUP to solve this, but I'm having trouble figuring out how to combine them.

=XMATCH(Test!A3,'THHN_THWN_THWN-2'!\$B\$4:\$B\$27): returns the correct row number for the conductor size I'm looking for.

=INDEX('THHN_THWN_THWN-2'!\$C\$4:\$N\$27,XMATCH(Test!\$A3,'THHN_THWN_THWN-2'!\$B\$4:\$B\$27),1): returns the first column of the correct row.

I feel like I'm one step away, but I can't figure out how to search the row for the column I'm looking for. Any thoughts?

6 Replies

# Re: Help with cross referencing using XMATCH and INDEX.

``=INDEX(\$B\$2:\$G\$2,SMALL(IF(OFFSET(\$B\$3:\$G\$3,MATCH(C14,\$A\$4:\$A\$10,0),0)>C15,COLUMN(\$A\$1:\$F\$1)),1))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. # Re: Help with cross referencing using XMATCH and INDEX.

You're right you are close. Leaving the column/row blank in INDEX (or giving it a 0) will return the whole row/column so in this case leaving the column blank (but you must still have the comma to indicate the column parameter):

``=INDEX('THHN_THWN_THWN-2'!\$C\$4:\$N\$27,XMATCH(Test!\$A3,'THHN_THWN_THWN-2'!\$B\$4:\$B\$27),)``

should return the whole row so then wrap it with XLOOKUP:

``=XLOOKUP(Test!\$B3, INDEX('THHN_THWN_THWN-2'!\$C\$4:\$N\$27,XMATCH(Test!\$A3,'THHN_THWN_THWN-2'!\$B\$4:\$B\$27),), 'THHN_THWN_THWN-2'!\$C\$2:\$N\$2, "not found", 1)``

But if you use this in multiple locations (or even not) I recommend you NAME those ranges. for example:
ConduitRng = 'THHN_THWN_THWN-2'!\$C\$2:\$N\$2
ConductorRng = 'THHN_THWN_THWN-2'!\$B\$4:\$B\$27
CountRng = 'THHN_THWN_THWN-2'!\$C\$4:\$N\$27
so then the formula READS like:

``=XLOOKUP(\$B3, INDEX(CountRng, XMATCH(\$A3, ConductorRng),), ConduitRng, "not found", 1)``

BTW since you used A3 as the conductor I am assuming B3 for the count.

and lastly you should be able to make an array output accordingly:

``=XLOOKUP(\$B3:\$B100, INDEX(CountRng, XMATCH(\$A3:\$A100, ConductorRng),), ConduitRng, "not found", 1)``

# Re: Help with cross referencing using XMATCH and INDEX.

I am all in favour of @mtarler 's use of names.  I would probably use two applications of XLOOKUP but there is very little to choose between the formulas.  Dressing it up a bit further with LET one gets

``````= LET(
conductorCountRng, XLOOKUP(conductorSize,  conductorSizeRng,  conductorCountArr),
conduitSize,       XLOOKUP(conductorCount, conductorCountRng, conduitSize, , 1),
conduitSize
)``````

Of course, it is possible to take things a step further and introduce a Lambda function, ConduitSizeλ, which refers to the formula above.

``````ConduitSizeλ
= LAMBDA(size, count,
LET(
conductorCountRng, XLOOKUP(size,  conductorSizeRng,  conductorCountArr),
conduitSize,       XLOOKUP(count, conductorCountRng, conduitSize, , 1),
conduitSize
)
)``````

giving the worksheet formula

``= ConduitSizeλ(conductorSize, conductorCount)``

Many spreadsheet users would hate this because it hides the working.  On the other hand, what is the benefit to the end user of seeing the working?

# Re: Help with cross referencing using XMATCH and INDEX.

Thank you! I appreciate the advice!

# Re: Help with cross referencing using XMATCH and INDEX.

Very interesting approach, I've never seen lambda functions in Excel. I'm assuming they're similar to Python's lambda functions?

# Re: Help with cross referencing using XMATCH and INDEX.

Yes, the new capabilities in Excel 365 share a common functional programming heritage with Python, including the use of Lambda helper functions such as MAP and SCAN.  In recent years, I have restricted my programming approach to array formulas (treating an relative references as errors) and use defined names (or Tables) for referencing all data (no A1-type references).