Ok, I'll do my best to explain this as thoroughly as possible.

I'm working on a Bill of Materials that uses a Pivot Table to count lengths
of wire sorted first by wire guage (size) and secondly by color. From this,
I'm trying to lookup (from a separate spreadsheet) the part number associated
with the particular guage/color. So, it's a two part lookup: first by guage,
then by color.

Enough research here, and I have an Index(1, Match(guage)*Match(color))
style forumla that works great, but only for the first color of each guage.
See, the pivot table formats in a particular way:

Guage Color Part Number
2 red xxxx
blk xxxx
4 red xxx
blk xxxx
gry xxxx
6 red xxxx
blk xxxx
yel xxxxx
grn xxxx
blu xxxx

and so forth, for many many lines, and the number of colors grows as the
guage changes as well. Autofilling the forumla I have causes issues with
the colors that don't have a guage in the cell to the left, because
technically, the guage cell is blank for that color. However, I cannot
re-format the pivot table at all, and merging the cells in the pivot table
won't work either.

Now, I'm not lazy, I'd simply fix it manually if it were one or two
spreadsheets, but this forumla will be used across literally thousands of
spreadsheets, and by people less Excel literate than I, so I need a rather
fool-proof (for lack of a better term) forumla I can autofill without
problems.

Thank you for any advice / help!