Double VLookup

[Guest]

I have the following data:

Table 1
Order # picks from Pool this Part #
777777 A1 123
666666 A2 456
555555 A3 789

Table 2
Part # has Qty in Pool
123 1 A2
456 2 A1
789 2 A3

I need a formula that tells me if the part is available from the pool where
the order will pick the part. I was thinking of a double VLookup but I don' t
know how to set up the formula. Perhaps using the IF function but still don't
know how to start.

 

[T. Valko]

Try this:

Table 1 in the range A2:C4
Table 2 in the range A11:C13

Enter this formula in D2 and copy down as needed:

=VLOOKUP(C2,A$11:C$13,3,0)=B2

Will return either TRUE or FALSE

Biff

 

[Roger Govier]

Hi

To get your double lookup, to ensure the part is available in the right
pool, I would insert a column before Order# in Table 1, and before Part#
in table 2

In Table 1, in the new column, enter
=D2&"|"&C2 and copy down
In Table 2 enter
=B2&"|"&D2 and copy down

Then use
=VLOOKUP(A2,Table2!$A$2:$D$1000,3,0)

If the part exists in the Pool, it will return the quantity there,
otherwise it will return #N/A showing that there is no part available.

 

[Guest]

Thank you Roger.

That's exactly what I did before posting the problem. I know it works fine
so, since I can't really use the double vlookup then I'll go with this
merge-lookup process. One more step but I get the results I want

Byron

 

[Roger Govier]

Hi Byron

Having copied the formula down column A on both sheets as far as the
extent of likely data, you can then hide column A on both sheets.