Excel Nested Formulas 1

[aarellano]

Hello,

I have been sppining my wheels for a couple of days and my brain is about to explote.

I know that you can do if((and in excel but here is what I am trying to do
In Sheet1 I have

    A       B       C
1  Item     Op      Rate
2  123      1        .25 
3  123      2        .75
4  123      3        .15

In Sheet2
(sheet2 comes from a download)

    A       B      C
1  Item    OP      Rate    
2  123      1
3  123      3
4  123      2

I need to bring in the rate for each item and each operation
I was trying to do a vlookup but it only looks at one cell
then I remembered that you can do an if((and statement.
I tried that but no luck.

Any Help is appreciated

Thanks

 

[SkipVought]

Hi,

Are you saying that there are other ITEMS, each with its own OPS and RATES?

Check Out the OFFSET function to define the RANGE for a given ITEM.

MATCH gives you the Row Offset. The Column offset can be ZERO.

COUNTIF give you the ROW COUNT, The Column Count is 1.

Then using the OFFSET defined range for a given Item, you can get the individual Rates, using Index & Match.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

I'd use SumProduct.

On Sheet2, range C2, put in the following formula:
[COLOR=blue white]=SumProduct((--(Sheet1!ItemList=Sheet2!A2) * --(Sheet1!OPList=Sheet2!B2) * --(Sheet1!$RateList))[/color]

You can use named ranges or change ItemList, OPList and RateList to the actual ranges you are using.

Note: Each section of the formula must contain the same number of rows.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John, your're right. Much better approch!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

...or use a helper column that concatenates the 2 lookup items and lookup on that instead

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[aarellano]

Thank you so much for all the help I got sick and had not had a chance to check. anotherhiggins I tried your suggestion and it worked for me. Thank you so very much!!

 

[AnotherHiggins]

Glad you got it sorted


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.