Take the 2-minute tour ×

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.

share|improve this question
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 Answer 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.

share|improve this answer

Your Answer


