vlookup
 

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 1s table A, F & I
columns placed in Sheet 2s A, B & C columns. I need to match the data in
Sheet 1s table A & F columns and Sheet 2s A & B columns to get the data
from Sheet 2s C column. The data from Sheet 2s C column will be placed in
Sheet 1s 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?

vlookup
 

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)



"JerryB" wrote:

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 1s table A, F & I
columns placed in Sheet 2s A, B & C columns. I need to match the data in
Sheet 1s table A & F columns and Sheet 2s A & B columns to get the data
from Sheet 2s C column. The data from Sheet 2s C column will be placed in
Sheet 1s 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?

vlookup
 

Thanks, either one of your solutions worked perfectly.

Jerry

"JMB" wrote:

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)



"JerryB" wrote:

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 1s table A, F & I
columns placed in Sheet 2s A, B & C columns. I need to match the data in
Sheet 1s table A & F columns and Sheet 2s A & B columns to get the data
from Sheet 2s C column. The data from Sheet 2s C column will be placed in
Sheet 1s 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?

vlookup
 

You're welcome. Thanks for the feedback.

"JerryB" wrote:

Thanks, either one of your solutions worked perfectly.

Jerry

"JMB" wrote:

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)



"JerryB" wrote:

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 1s table A, F & I
columns placed in Sheet 2s A, B & C columns. I need to match the data in
Sheet 1s table A & F columns and Sheet 2s A & B columns to get the data
from Sheet 2s C column. The data from Sheet 2s C column will be placed in
Sheet 1s 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?