Double VLookup
 

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.

Double VLookup
 

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

"Byron720" wrote in message
...
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.

Double VLookup
 

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.


--
Regards

Roger Govier


"Byron720" wrote in message
...
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.

Double VLookup
 

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

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.


--
Regards

Roger Govier


"Byron720" wrote in message
...
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.

Double VLookup
 

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.

--
Regards

Roger Govier


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

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.


--
Regards

Roger Govier


"Byron720" wrote in message
...
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.