vlookup multiple rows

[dep]

Help! I have two large spreadsheets I want to compare. I want to compare
the values in 4 cells (within the same row) in one spreadsheet and search a
second spreadsheet to match the values in 4 cells (within the same row). When
the condition is met and the 4 cells between the two spreadsheets match, I
want to return a value from the second spreadsheet (from the row in which all
4 values match) to a cell in the first spreadsheet. I am wondering if there
is a way to do this?

Here is a copy of the formula I've tried, which doesn't work.

=VLOOKUP((AND('Panels schedule 2'!I4=Reference!A42,'Panels schedule
2'!M4=Reference!D42,Reference!C42="Top",'Panels schedule
2'!J4=Reference!B42)),Reference!A42:I79,5,TRUE)

Any ideas?

 

[Paul Corrado]

If all of the records in the source table are unique and you are trying
return a numeric result you can use

=SUMPRODUCT((A2:A11="first criteria")*(B2:B11="second
criteria")*(C2:C11="third criteria")*(D211="fourth criteria")*(E2:211))

Adjust ranges as necessary

 

[Myrna Larson]

If the result is not numeric, you can use a variation in which SUMPRODUCT
returns the row number, and you use that as the argument to INDEX:

=INDEX(E:E,SUMPRODUCT(
(A2:A11="first criterion")
*(B2:B11="second criterion")
*(C2:C11="third criterion")
*(ROW(D211)))

all on one line, of course.

But this will work correctly ONLY if there's just one match. If there's a
match in row 3 and row 8, it will return the value from E11 -- not what is
wanted.

 

[dep]

Unfortunately, the records I'm evaluating in the source table are not all
numeric. some of them are text, so I don't think this will work.

Thanks anyway.

 

[Paul Corrado]

I believe LOOKUP will also work. With your four criteria in A1,B1,C1 & D1
on WorksheetA and the data in WorksheetB

=LOOKUP(A1&B1&C1&D1,'WorksheetB!'A211,E2:E11)

Should work. (I believe I have the worksheet 2 reference correct )

 

[Myrna Larson]

That doesn't work for me, Paul. I get #NA!

First of all, LOOKUP requires the table to be sorted, which I don't think we
can count on.

I wonder about the syntax you used:

If you use 3 arguments with LOOKUP, the second and third are supposed to be
vectors -- a single row or column. If the second is a rectangle, there isn't
supposed to be a 3rd argument.

That said, some fiddling around with it convinced me that with the syntax you
used, Excel doesn't report an error, but treats it as though your first
argument was A2:A11 rather than A211.

If that's what's happening, your target is a concatenation of the values from
the 1st 4 columns, and you look for it in only in column A. It will not match
unless B11 are empty and there's a row in the table with values only in
column A, will it?

Your table neads a column that consists of the concatenation of the 4 values
in that row, no? Looking at that column, you would find a match.

 

[Paul Corrado]

I thought the LOOKUP solution was too easy. I tried it before posting on
a simple data set and it worked. But as you point out, it was only looking
at the first column of data in the lookup range. I didn't check it in that
much detail.

 

[Aladin Akyurek]

Expand the data area in the Reference sheet with an additional colum, for
example, with column J, using:

In J42 enter & copy down:

=A42&","&D42&","&C42&","&B42

Now use:

=INDEX(Reference!$E$42:$E$79,MATCH('Panels schedule 2'!I4&","&'Panels
schedule 2'!M4&","&"Top"&'Panels schedule 2'!J4,Reference!$J$42:$J$79,0))