Help with VLookup

[bgraczyk]

Is it possible to set up a V-Lookup function to return values from mor
than one cell?

I am trying to confirm that 2 cells of data from one sheet matches
cells of data from another sheet.

Example:

In the attached file I want to return the "quantity" value from th
sheet named "Customer BOM" and confirm that it matches the quantity on
the sheet named "Oracle BOM" for each "Item".

Any help would be great!

Thanks, Bet

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=68696

 

[Frank Kabel]

Hi
without looking at your example try the following type of array formula
(entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=value1)*(B1:B100=value2),0))

 

[Max]

Try this:

In Oracle BOM
------------------

Select C22

Put in the *formula bar*:
=IF(ISNA(MATCH(A2,'Customer BOM'!A:A,0)),"",VLOOKUP(A2,'Customer
BOM'!A:B,{1,2},FALSE))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

This'll return the Check Item in C2 and the Check Quantity in D2

Put in E2: =IF(AND(B2="",D2=""),"",D2=B2)

Select C2:E2 and copy down

Col E will return TRUE if the quantity for the item matches in both sheets,
FALSE if otherwise.
It'll return blanks "" if there's no quantity in both cols B and D.

You could then do a Data > Filter > Autofilter on col E
to extract either FALSE (items with unmatched quantities)
or TRUE (items with matched quantities)

---
Where there's the possibility of duplicate items (Part No.) listed in col A
in Customer BOM,

e.g:
ABC 3
ABC 2
XYZ 1
QRS 1
XYZ 2
QRS 3
etc

an alternative way to return the total quantity for the items specified in
col A in Oracle BOM
from Customer BOM would be to put in say, G2 in Oracle BOM:

=SUMPRODUCT(('Customer BOM'!$A$2:$A$4=A2)+0,'Customer BOM'!$B$2:$B$4)

Copy G2 down