Lookup two columns

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

I want to compare the contents of two (adjacent) cells in one sheet with two
adjacent cells in another sheet (within one workspace) and if the *pair* of
cells are the same, deliver the value in the cell a few columns along (if
you know what I mean - like lookup but comparing two cells). The cells are
not sorted.

Any ideas?

Cheers.

Bobby
 
If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then

=IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match")

If you have to "lookup" A1-B1 against the whole columns of A and B on
sheet2, then kyou could Concatenate the two columns on both sheets and do a
VLOOKUP on the concatenated columns...........

Vaya con Dios,
Chuck, CABGx3
 
Data list in Sheet1 - A1:C100
Values to look up are in Sheet2 - Y1 and Z1
Values to find are in columns A and B of data list.
Look for Y1 value in column A
Look for Z1 value in column B
Value to return is in column C of data list.

Enter this array formula in sheet2:

=INDEX(Sheet1!C1:C100,MATCH(Y1&Z1,Sheet1!A1:A100&Sheet1!B1:B100,0))

This is an array formula, so it must be entered with CSE (<Ctrl> <Shift>
<Enter>), which will *automatically enclose the formula with curly brackets.

A #VALUE! error means you didn't use CSE.
A #N/A error means there are no matching pairs of cells.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I want to compare the contents of two (adjacent) cells in one sheet with two
adjacent cells in another sheet (within one workspace) and if the *pair* of
cells are the same, deliver the value in the cell a few columns along (if
you know what I mean - like lookup but comparing two cells). The cells are
not sorted.

Any ideas?

Cheers.

Bobby
 
Another one...

x1 and y1 are the cells to match.
A1:A10 and B1:B10 on Sheet2 are looked at.
something Z1:z10 on sheet2 is returned

=INDEX(Sheet2!Z1:Z10,MATCH(1,(Sheet2!A1:A10=X1)*(Sheet2!B1:B10=Y1),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Back
Top