vlookup

J

JerryB

I have a workbook with 2 worksheets. Sheet 1 has a table ranging from A1 to
M200. Sheet 2 has a table with matching data from Sheet 1’s table A, F & I
columns placed in Sheet 2’s A, B & C columns. I need to match the data in
Sheet 1’s table A & F columns and Sheet 2’s A & B columns to get the data
from Sheet 2’s C column. The data from Sheet 2’s C column will be placed in
Sheet 1’s I column.
Example: Sheet1 A1 Part#
Sheet1 F1 Vendor
Sheet1 I1 Shelf Life

Sheet2 A1 Part#
Sheet2 B1 Vendor
Sheet2 C1 Shelf Life

The reason I need to match Part# and Vendor is that different vendors for
the same Part# may have different shelf lives

The question is can I look up 2 different variables of an item and get a
single result?
 
J

JMB

I will assume your actual data in Sheet2 is in rows 2 - 100 (change ranges as
needed). In Sheet1, cell I2, try:

=INDEX(Sheet2!C$2:C$100, MATCH(F2&A2, Sheet2!B$2:B$100&A$2:A$100, 0))
confirmed with Cntrl+Shift+Enter (or you'll get #VALUE! error)

Or you could concatenate the data on Sheet2 to create a key (in a column to
the left of your data, I am assuming column A is empty)
=B2&A2 (copy it down)
then use VLookup
=VLOOKUP(F2&A2, Sheet2!A$2:C$100, 3, 0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top