Lookup Issue due to formatting

[The Intern]

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!

 

[T. Valko]

Try this...

Assume row 1 are column headers.

Data in the range A2:C11

Named ranges:

Guage = A2:A11
Color = B2:B11
PN = C2:C11

F2 = guage lookup
G2 = color lookup

=LOOKUP(2,1/(Color=G2)/(LOOKUP(ROW(Guage),ROW(Guage)/(Guage<>""),Guage)=F2),PN)

 

[The Intern]

That does correctly look up the part numbers, but as before it only works for
the first instance of each gage. I do like that it doesn't need a
shft-ctrl-enter to make it work.

Is there a way to have the function recursively look in the cell above for
the gage if the current gage cell is empty?

 

[T. Valko]

Hmmm...

It works for me. Here's a small sample file that demonstrates this:

xLookup.xls 15kb

http://cjoint.com/?fDunFpSEHc

Select a guage and a color and you'll get the correct pn. If a color is not
available for a particular guage then you'll get a result of #N/A.

 

[The Intern]

After looking at your example, I see where I wasn't clear about the issue.
My database has explicit listing, meaning for every color, there is a cell
corresponding uniquely that gives gage (I spelled it wrong earlier).

In the formula, the Gage, Color and PN ranges are complete, it's the F2 and
G2 that are sometimes blank. So, in otherwords, I'm not looking up numbers
in an incomplete batabase, I'm using an incomplete list as the criteria for
the search.

In the example file you provided, copy the forumla into the cells to the
right of the PN list, and use the Gage & Color lists as the search input.

 

[T. Valko]

In other words, you're wanting to return the guage by looking up the color
and part number?