-1

Sheet 1 gets values from Sheet 2.

Sheet 2 has hundreds of rows, but only 4 columns. (Cols A & C are names, Cols B & D are numbers).

Cell XX (sheet 1): Looks in Sheet 2, For "Bill" (col A) and "Jill" (col C) where they BOTH appear in the same row, then returns number from col B (from row where "Bill" and "Jill" are found) to Cell XX in sheet 1.

If "Jill" is in col A and "Bill" is in col C - then conditions are not met, and do not return value.

How do I write this?

I am so confused. PLEASE, someone help me?

Lookup Sheet 2 For "Bill" (Col A2:A300) AND "Jill" (Col C2:C300) Get number in Col B.

CanucksGirl
  • 1
  • 1
  • 3
  • I would be really happy if someone could just tell me what type of function I should use for this! I thought it should be an HLOOKUP, but I couldn't get that to work. Was I even on the right track, or should I be using something else? – CanucksGirl Nov 10 '12 at 20:12

1 Answers1

1

One solution is to create a helper column E. Use this formula in the first row (i.e. cell E1):

 =$A1 & "-" & $A3

and fill this down for the other rows. If ColA = "Bill" and ColB = "Jill", then ColE will be "Bill-Jill".

You can then do a MATCH to find out with row has "Bill-Jill", and pick up the corresponding value from ColB:

=IF(ISNA(MATCH("Bill-Jill";e:e;0));"No match";INDEX(b:b;MATCH("Bill-Jill";e:e;0)))

The MATCH formula will find out which row contains "Bill-Jill", and the INDEX formula will pick up that row from another column. MATCH will return #NA if there isn't a matching cell, and this will be captured by the ISNA check.

I'm not sure if OpenOffice supports the MATCH function - it's definitely part of Excel though.

Mischinab
  • 2,751
  • 1
  • 20
  • 15