Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in array formula - loss of inspiration
Hello all
I am trying to solve a problem in Excel with array formula and am having limited success. Perhaps somebody can help. On one sheet I have a list of various bits of data, e.g. weights of objects. To keep things simple, let's say; ITEM WEIGHT UNIT One 10 kg Two 20 lb Three 1.2 tons Four 1 oz These ranges are then named dynamically and become LItems, LWeights and LUnits. I need to sum up the weights of all the items but resolved into one target weight, the unit of which is specified elsewhere (TargetUnit). This requires a conversion from the specified weight to the target weight, there is a table of conversion factors for this, e.g. kg lb tons oz kg 1 u v w lb 1/u 1 x y tons 1/v 1/x 1 z oz 1/w 1/y 1/z 1 I define the list of units as another named range (ListOfUnits) and then use the index function to find the appropriate conversion factor. E.g. =INDEX(FactorsTable,MATCH(LUnits,ListOfUnits,0),MA TCH(TargetUnit,ListOfUnits,0)) This works well and returns the correct conversion factor for a single value entry. But I can't for the life of me get it to work in an array formula. The penny hasn't dropped as to why... I'm sure I've solved problems like this before with Excel. I've tried a number of array formulas but they all seem to use the unit specified in the top row of the data (in this case 'kg') for all the conversions, rather than the correct unit. The formula I last tried was: {=SUM(LWeights*(INDEX(FactorsTable,MATCH(IF(OR(ISB LANK(LUnits),LUnits=""),TargetUnit,LUnits),ListOfU nits,0),MATCH(TargetUnit,ListUnits,0))))} The IF statement is there to guard against no units being specified, in which case the target unit is assumed. There is an easy solution; I can perform the conversion on the data page and then just sum all of these. Unfortunately I can't do that in this case, I need to create a spreadsheet where the data page is purely data only, no calculations. Thanks for any help. Matt (reply to NG or remove the capital letters from my email) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
i need help with a lookup and/or array type formula | Excel Worksheet Functions | |||
Array formula lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Lookup Array Formula | Excel Worksheet Functions |