I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row
and the corresponding match in spreadsheet B would also have to be in the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
I sure hope I was clear enough... Any help is appreciated!!
--
tb

Hi!

When you say "spreadsheetA" and "spreadsheetB", do you mean separate files?

Here's how to do it using 2 worksheets in the same workbook.

In sheet1 C2 enter any one of these formulas as an array using the key combo
of CTRL,SHIFT,ENTER:

=INDEX(Sheet2!C2:C11,MATCH(1,(Sheet2!A2:A11=A2)*(S heet2!B2:B11=B2),0))

=INDEX(Sheet2!C2:C11,MATCH(A2&B2,Sheet2!A2:A11&She et2!B2:B11,0))

=INDEX(Sheet2!C2:C11,MATCH(A2&"@"&B2,Sheet2!A2:A11 &"@"&Sheet2!B2:B11,0))

You might be able to use a much less complex formula depending on the the
data type you're trying to return but since you didn't include those
details.................we're left to guess!

Biff

"Tiziano" wrote in message
...
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row
and the corresponding match in spreadsheet B would also have to be in the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
I sure hope I was clear enough... Any help is appreciated!!
--
tb

I am looking for the answer to a similar problem. I have a calendar
format on sheet1 and there are 5 rows in each date section and those
rows correspond to peoples names which are listed in the farmost left
colum of the spreadsheet. On sheet2 there is a data table with column 1
= date column 2 = person column 3 = location.

The idea here is to return on a calendar where each person will be on a
given day by referencing a table. Any help?


+-------------------------------------------------------------------+
|Filename: MR Revenue.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4091 |
+-------------------------------------------------------------------+

--
MrvinGover
------------------------------------------------------------------------
MrvinGover's Profile: http://www.excelforum.com/member.php...fo&userid=5124
View this thread: http://www.excelforum.com/showthread...hreadid=490074

Hi!

I gave your file a quick look-over.....

You can use a formula "like" this:

=INDEX('Data Table'!C2:C31,MATCH(1,('Data Table'!E2:E31=A31)*('Data
Table'!B2:B31=B25),0))

Some of the "names" do not match EXACTLY:

On the Calendar sheet the "name" is "Waitlist / Misc." but on the Data Table
sheet that "name" is listed as "Misc".
You need to make them all the same in both locations.

That'll get you started. I don't have any more time tonight!

Biff

"MrvinGover" wrote
in message ...

I am looking for the answer to a similar problem. I have a calendar
format on sheet1 and there are 5 rows in each date section and those
rows correspond to peoples names which are listed in the farmost left
colum of the spreadsheet. On sheet2 there is a data table with column 1
= date column 2 = person column 3 = location.

The idea here is to return on a calendar where each person will be on a
given day by referencing a table. Any help?


+-------------------------------------------------------------------+
|Filename: MR Revenue.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4091 |
+-------------------------------------------------------------------+

--
MrvinGover
------------------------------------------------------------------------
MrvinGover's Profile:
http://www.excelforum.com/member.php...fo&userid=5124
View this thread: http://www.excelforum.com/showthread...hreadid=490074

Thank you for your reply Biff, it may be me but I cant seem to get the
thing to work. I event tried the small little example that Microsoft
provides in the help section under Index and Match and I cant get
either one of them to work, I changed the names to match and i built a
new formula using yours as a guide (not copy and paste) and it just
returns a #NA cell value. Were you able to actually get the cell to
reference one correctly?

I have not used Index and Match functions before, I was looking towards
Vlookup but that was the wrong direction.

Thanks

MG


--
MrvinGover
------------------------------------------------------------------------
MrvinGover's Profile: http://www.excelforum.com/member.php...fo&userid=5124
View this thread: http://www.excelforum.com/showthread...hreadid=490074

Thanks, Biff.
Yes, I mean two separate Excel files with "spreadsheet A" and "spreadsheet
B".
Does your formula change given this information? (I am not much of an Excel
expert...)
Thanks again.
--
tb

"Biff" wrote in message
...
Hi!

When you say "spreadsheetA" and "spreadsheetB", do you mean separate
files?

Here's how to do it using 2 worksheets in the same workbook.

In sheet1 C2 enter any one of these formulas as an array using the key
combo
of CTRL,SHIFT,ENTER:

=INDEX(Sheet2!C2:C11,MATCH(1,(Sheet2!A2:A11=A2)*(S heet2!B2:B11=B2),0))

=INDEX(Sheet2!C2:C11,MATCH(A2&B2,Sheet2!A2:A11&She et2!B2:B11,0))

=INDEX(Sheet2!C2:C11,MATCH(A2&"@"&B2,Sheet2!A2:A11 &"@"&Sheet2!B2:B11,0))

You might be able to use a much less complex formula depending on the the
data type you're trying to return but since you didn't include those
details.................we're left to guess!

Biff

"Tiziano" wrote in message
...
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same
row
and the corresponding match in spreadsheet B would also have to be in
the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet
A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
I sure hope I was clear enough... Any help is appreciated!!
--
tb

T,

Did you get this to work? If so would you attach a copy of your
spreadsheet so I can glean some ideas on how to do it for myself?


--
MrvinGover
------------------------------------------------------------------------
MrvinGover's Profile: http://www.excelforum.com/member.php...fo&userid=5124
View this thread: http://www.excelforum.com/showthread...hreadid=490074

Does your formula change given this information?

The same basic structure still applies, however, you'd have to include the
path to the source file:

=INDEX('[File B.xls]Sheet1'!$C$2:$C$11,MATCH(A2&B2,'[File
B.xls]Sheet1'!$A$2:$A$11&'[File B.xls]Sheet1'!$B$2:$B$11,0))

The best way to do this is to have both files open as you write the formula
and use your mouse to point to the references. That way Excel will "plug in"
the path for you.

Biff

"Tiziano" wrote in message
...
Thanks, Biff.
Yes, I mean two separate Excel files with "spreadsheet A" and "spreadsheet
B".
Does your formula change given this information? (I am not much of an
Excel
expert...)
Thanks again.
--
tb

"Biff" wrote in message
...
Hi!

When you say "spreadsheetA" and "spreadsheetB", do you mean separate
files?

Here's how to do it using 2 worksheets in the same workbook.

In sheet1 C2 enter any one of these formulas as an array using the key
combo
of CTRL,SHIFT,ENTER:

=INDEX(Sheet2!C2:C11,MATCH(1,(Sheet2!A2:A11=A2)*(S heet2!B2:B11=B2),0))

=INDEX(Sheet2!C2:C11,MATCH(A2&B2,Sheet2!A2:A11&She et2!B2:B11,0))

=INDEX(Sheet2!C2:C11,MATCH(A2&"@"&B2,Sheet2!A2:A11 &"@"&Sheet2!B2:B11,0))

You might be able to use a much less complex formula depending on the the
data type you're trying to return but since you didn't include those
details.................we're left to guess!

Biff

"Tiziano" wrote in message
...
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same
row
and the corresponding match in spreadsheet B would also have to be in
the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet
A
match with the comparison values in cells A30:B30 of spreadsheet B,
then
show the value that is in column C30 of spreasheet B.
I sure hope I was clear enough... Any help is appreciated!!
--
tb

MrvinGover Wrote:
T,

Did you get this to work? If so would you attach a copy of your
spreadsheet so I can glean some ideas on how to do it for myself?

Here is a simplified version of my spreadsheet. The formula in column
C
(sheet1) looks up the data in sheet2. In this example, the data in
sheet1
is the same as the one in sheet2 (as I said, this is a simplified
version of
my spreadsheet), but the important thing is that the formula suggested
by
Biff works...
Don't forget to hit CTRL+SHIFT+ENTER when you are finished writing the
formula.
--
tb


+-------------------------------------------------------------------+
|Filename: file1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4093 |
+-------------------------------------------------------------------+

--
tiziano1
------------------------------------------------------------------------
tiziano1's Profile: http://www.excelforum.com/member.php...o&userid=29319
View this thread: http://www.excelforum.com/showthread...hreadid=490074

Ah ha!

You don't need to use those "complex" array formulas. You can use this:

=SUMPRODUCT(--(Sheet2!A$2:A$1116=A2),--(Sheet2!B$2:B$1116=B2),Sheet2!C$2:C$1116)

I replaced those array formulas with the one above and reduced the file size
from 271kb to 244kb. Initial calculation is now also "instant" versus ~5
seconds.

MrvinGover, on the other hand, can't use that type of formula. They're
returning text values.

Biff

"tiziano1" wrote in
message ...

MrvinGover Wrote:
T,

Did you get this to work? If so would you attach a copy of your
spreadsheet so I can glean some ideas on how to do it for myself?

Here is a simplified version of my spreadsheet. The formula in column
C
(sheet1) looks up the data in sheet2. In this example, the data in
sheet1
is the same as the one in sheet2 (as I said, this is a simplified
version of
my spreadsheet), but the important thing is that the formula suggested
by
Biff works...
Don't forget to hit CTRL+SHIFT+ENTER when you are finished writing the
formula.
--
tb


+-------------------------------------------------------------------+
|Filename: file1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4093 |
+-------------------------------------------------------------------+

--
tiziano1
------------------------------------------------------------------------
tiziano1's Profile:
http://www.excelforum.com/member.php...o&userid=29319
View this thread: http://www.excelforum.com/showthread...hreadid=490074