Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |