Compare 4 columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deb - 16 May 2006 07:44 GMT

Hi guys,

I'm hoping someone can help me out ! I have 2 worksheets and what i need to
do is compare 2 cols from one to 2 cols in another - if they match then bring
across a value from an X col.
It's basically what a Vlookup does but instead of comparing one col and
brining acorss a value i want to make sure that two cols match before it
brings across a value.

So in lay terms :

If Value in cell Sheet1!A1 = a value in Sheet2!$A$500
AND Then
value in Sheet1!$B$1 = The value in cell D from the same row in which you
found the first matching value
THEN
bring across the value in cell F from that same row

Thanks guys and gals!!
Deb

Reply to this Message

Max - 16 May 2006 09:14 GMT

Perhaps something along these lines ..

In Sheet3,

Put in the formula bar for say, B1:
=INDEX(Sheet1!$F$1:$F$1000,MATCH(1,(Sheet2!$A$1:$A$1000=Sheet1!A1)*(Sheet2!$D$1:$D$1000=Sheet1!B1),0))

then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

As-is, the array formula can then be copied down col B
to return correspondingly for other pairs of look-up values
in Sheet1's A2:B2, A3:B3, etc

Adapt the ranges (eg: Sheet2!$A$1:$A$1000) to suit.
Ranges must be identical in size,
and we can't use entire col references (eg: A:A, B:B, etc)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi guys,
>
[quoted text clipped - 16 lines]
> Thanks guys and gals!!
> Deb

Reply to this Message