MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looking for Easier Formula

Posted by Kevin on February 04, 2002 8:54 PM

I have a list of ten names (let's say in cells a1 thru a10). Each name has certain data associated only with that name. I want to be able to select a few names by placing a 1,2 and 3 next to their names (say in b1 thru b10). I then want three other cells to look at b1 thru b10 and compute a formula based upon the finding of a 1,2 or 3 . Can anyone hlep me with this? If I need to provide more clarity let me know.

Posted by Derek on February 05, 2002 12:10 AM

When you say "three other cells" compute a formula I assume each cell computes on data indicated by either the 1, the 2 or the 3 (not all three together)? I also assume you don't want the name in column A for your computation but some other data on the same row - lets say in column C

These formulas will grab your data from column C of the row in which you have typed either 1, 2 or 3 in column B -
formula in D1 (for 1) =INDIRECT(ADDRESS(MATCH(1,$B:$B,0),3))
formula in E1( for 2) =INDIRECT(ADDRESS(MATCH(2,$B:$B,0),3))
Formula in F1 (for 3) =INDIRECT(ADDRESS(MATCH(3,$B:$B,0),3))

The number 3 at the end of each formula denotes the column number of the data you want (3 = column C). If your data is in a different column, change this number to suite).
Integrate these formulas into your computation formula.

Hope this helps