Referencing cells to return a value

[Baron J79]

I've been trying to solve this for a few days and now am at my wit's end.

I have two spreadsheets and wish to find two values in a single row in sheet 1 which are in sheet 2. If the values occur in sheet 1, I want the formulae to return a corresponding sheet reference from sheet 1. If they do not, I want the formula to return a corresponding sheet reference from sheet 2. An illustration:

Sheet 1

System Product Effect on budget
AD T24 -929,535
AD T26 -45,505
AD T29 141,243
AD T50 55,077
AD T53 81,092

Sheet 2

System Product Cost
AD T24 1,734,499
AD T28 1,265,562
AD T29 393,503

I need a formula that will search sheet 1 for a system and product that exists in sheet 2. If both do occur in sheet 1, I wish it to return the corresponding "Effect on Budget" figure for that occurence. If it does not, I need it to return the "Cost" figure that corresponds to the search values in sheet 2.

For example, searching for AD and T24 would return -929,535, as AD and T24 occur in both sheets. AD and T28, however, would give 1,265,562, as it only occurs in sheet 2.

I would like the formula to be in sheet 2.

I've trieds all sorts of things, including vlookup, ifs, ands, combinations of all these, etc., but nothing seems to work the way I want it to. Any ideas?

[Richard Buttrey]

I've been trying to solve this for a few days and now am at my wit's end.

I have two spreadsheets and wish to find two values in a single row in sheet 1 which are in sheet 2. If the values occur in sheet 1, I want the formulae to return a corresponding sheet reference from sheet 1. If they do not, I want the formula to return a corresponding sheet reference from sheet 2. An illustration:

Sheet 1

System Product Effect on budget
AD T24 -929,535
AD T26 -45,505
AD T29 141,243
AD T50 55,077
AD T53 81,092

Sheet 2

System Product Cost
AD T24 1,734,499
AD T28 1,265,562
AD T29 393,503

I need a formula that will search sheet 1 for a system and product that exists in sheet 2. If both do occur in sheet 1, I wish it to return the corresponding "Effect on Budget" figure for that occurence. If it does not, I need it to return the "Cost" figure that corresponds to the search values in sheet 2.

For example, searching for AD and T24 would return -929,535, as AD and T24 occur in both sheets. AD and T28, however, would give 1,265,562, as it only occurs in sheet 2.

I would like the formula to be in sheet 2.

I've trieds all sorts of things, including vlookup, ifs, ands, combinations of all these, etc., but nothing seems to work the way I want it to. Any ideas?

With your example sheet1 data in A1:A6 - including the column labels, add a new helper column A. In A2 enter:

Please Login or Register  to view this content.

and copy this down to row 6.

On sheet 2 do similarly, add a new helper column A and again enter the formula above inA2 and copy down.

Now in E2 enter:

Please Login or Register  to view this content.

and copy down to E4

HTH

[rylo]

Hi

If your data in sheet1 is in A1:C6, and the data in sheet2 is in A1:C4, then:

sheet2!E2: =IF(SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet2!A2),--(Sheet1!$B$2:$B$6=Sheet2!B2),(Sheet1!$C$2:$C$6)),SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet2!A2),--(Sheet1!$B$2:$B$6=Sheet2!B2),(Sheet1!$C$2:$C$6)),C2)

Copy down to E4.

HTH

rylo

[Baron J79]

Thanks, Richard. It works an absolute treat.

I knew there was something missing and it was the ISERROR function.

Would still be stuck if you hadn't helped.

Baron J79 out.