If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
lookup mutliple values to return one value
I am trying to find a way to lookup more than one value in a row (vlookup) to
return a value in a corresponding row. We have a list of values in column A (multiple entries of the same value) and a sub-value in column B (call it a part and a sub assembly in the columns). Each item in column B corresponds to an added value to the original part (column C). For example, if column A has two parts, 1 and 2, and 10 different sub-assemblies for each part, there are 10 rows in column A marked 1, and 10 marked 2. Column B has subassemlies 1-10 for part 1, and 1-10 for part 2. Column C is the added value (for simplicity, each sub assembly costs a dollar). I want to know how much the part costs if only the first 5 subassemblies are added to the part (I know, $5). I have a "work in progress sheet that shows the part and how far along it is in the assembly (part 1, sub 4), so I would expect to return $4. I cheated the formula by concatenating the two fields on each of the spreadsheets and doing the vlookup based on that, but it's not a pretty way to do it. Any help would be greatly appreciated. |
#2
|
|||
|
|||
lookup mutliple values to return one value
One interp ..
Assuming the data in cols A and B is within A2:B100, and the inputs are made in: C1: part# (eg: 1) D1: sub-assembly (eg: 2) E1: unit cost per sub-assembly (eg: 1) we could try in F1: =IF(OR(C1="",D1="",E1=""),"",E1*SUMPRODUCT(($A$2:$ A$100=C1)*($B$2:$B$100=D1) )) F1 can be copied down to compute correspondingly for other variations of inputs in C2:E2, C3:E3, etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- David Pelizzari; "IS Manager" wrote in message ... I am trying to find a way to lookup more than one value in a row (vlookup) to return a value in a corresponding row. We have a list of values in column A (multiple entries of the same value) and a sub-value in column B (call it a part and a sub assembly in the columns). Each item in column B corresponds to an added value to the original part (column C). For example, if column A has two parts, 1 and 2, and 10 different sub-assemblies for each part, there are 10 rows in column A marked 1, and 10 marked 2. Column B has subassemlies 1-10 for part 1, and 1-10 for part 2. Column C is the added value (for simplicity, each sub assembly costs a dollar). I want to know how much the part costs if only the first 5 subassemblies are added to the part (I know, $5). I have a "work in progress sheet that shows the part and how far along it is in the assembly (part 1, sub 4), so I would expect to return $4. I cheated the formula by concatenating the two fields on each of the spreadsheets and doing the vlookup based on that, but it's not a pretty way to do it. Any help would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return Multiple Values via Subquery | JDRaven | Running & Setting Up Queries | 3 | January 5th, 2006 08:38 PM |
lookup serch term and return sum of all values | soilcon1 | Worksheet Functions | 2 | December 22nd, 2005 12:03 AM |
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Sam via OfficeKB.com | Worksheet Functions | 6 | November 29th, 2005 03:27 PM |
how to lookup a value and return multiple corresponding values | Asthee | Worksheet Functions | 1 | November 5th, 2005 01:49 PM |
Return Range of Numerical Values in Single Column based on Frequency Percentage | Sam via OfficeKB.com | Worksheet Functions | 9 | October 28th, 2005 11:01 PM |