trouble with (complicated) vlookup searching another workbook

[sporenta]

Sorry for the long entry, I just want to provide all info.

I have created a vlookup formula with a dynamic table_array. I
basically looks for a value (the number assigned to a piece o
equipment) in cell C8 in a series of sheets (each named after th
students who are checking out gear). If the formula doesn't find th
number, it looks in the next sheet and the next until it finds it. I
then returns the value I'm looking for--in this case, the name of
student who has checked out a piece of equipment, located in cell D8--t
an inventory sheet named after the type of gear being checked ou
(Sleeping Bags, for example).

Here is the formula I would put on sheet "Sleeping Bags" in cell C3 whe
cell B3 = the sleeping bag number being looked up:

{=VLOOKUP($B3,INDIRECT("'"&INDEX(StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$B3)>0),0)))&"'!$c$8:d$8"),2,FALSE)}

"StudentList" referes to a named range of students on sheet "Students"
Each student has a sheet name that matches his or her name on th
StudentList.

So far it works great. For example, on sheet "Smith, Johnny", I ente
that he checked out Sleeping Bag #3 in C8, and then on the sheet title
"Sleeping Bags", the formula puts his name next to sleeping bag #3
Perfect.

The problem is that I have 11 inventories and 32 students. Too man
sheets to keep it neat. I want to separate the inventory sheets from th
student checkout sheets and have the vlookup search the new "Checkout
workbook. So now the formula becomes:

{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)>0),0)))&"'!$c$8:d$8"),2,FALSE)}

When shft+cntrl+enter the formula, I get #n/a.

Any ideas

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[lhkittle]

I didn't understand your formula. What does the -- signify? It looks like you're trying to decrement a variable, but as far as I know standard Excel doesn't allow for this. If there is such an operator, can you tell me where it's documented?

Howard

Hi Howard,

(MATCH(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$B3)>0),0))

I believe this returns TRUE or FALSE if COUNTIF > 0 and the -- converts TRUE and FALSE to 1 and 0.

The 1 or 0 are then later in the formula to do some magic I really don't understand.

I got the explanation from Peo Sjoblom in 2006 and have really never been fully capable of understanding the entire formula. But it has been useful.

Regards,
Howard (L. Howard responding to Howard)

 

[Claus Busch]

Hi,

Am Tue, 6 Aug 2013 16:54:08 +0100 schrieb sporenta:

{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)>0),0)))&"'!$c$8:d$8"),2,FALSE)}

When shft+cntrl+enter the formula, I get #n/a.

INDIRECT requires a open workbook

Regards
Claus B.