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" wrote:

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.

--
Biff
Microsoft Excel MVP


"The Intern" wrote in message
...
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" wrote:

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)


--
Biff
Microsoft Excel MVP