Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_shee t2=A2)*(Location_range_sheet2=B2),0))

entered with ctrl + shift & enter



Regards,


Peo Sjoblom



"Sunryzz" wrote:

Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

After the word MATCH, what is the 1 for? It's not working for me and I think
I understand everything but that, so I thought that might give me the answer.
Thanks for your help!!

"Peo Sjoblom" wrote:

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_shee t2=A2)*(Location_range_sheet2=B2),0))

entered with ctrl + shift & enter



Regards,


Peo Sjoblom



"Sunryzz" wrote:

Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

The formula is doing a logical test and multiplying that against another
logical test (of an array range.) So, for each row in the range, the first
test is going to either be true or false and likewise the 2nd test will
either be true or false. When you multiply true*true you get a 1 (which is
what the match is testing) Any thing else will result in 0 (true*false,
False*true, and false*false)

--
Kevin Vaughn


"Sunryzz" wrote:

After the word MATCH, what is the 1 for? It's not working for me and I think
I understand everything but that, so I thought that might give me the answer.
Thanks for your help!!

"Peo Sjoblom" wrote:

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_shee t2=A2)*(Location_range_sheet2=B2),0))

entered with ctrl + shift & enter



Regards,


Peo Sjoblom



"Sunryzz" wrote:

Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

I keep getting N/A for the answer, but I can't figure out why. Here is my
formula.

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=Sheet1 !A2)*(Sheet2!B1:B10=Sheet1!B2),0))

"Kevin Vaughn" wrote:

The formula is doing a logical test and multiplying that against another
logical test (of an array range.) So, for each row in the range, the first
test is going to either be true or false and likewise the 2nd test will
either be true or false. When you multiply true*true you get a 1 (which is
what the match is testing) Any thing else will result in 0 (true*false,
False*true, and false*false)

--
Kevin Vaughn


"Sunryzz" wrote:

After the word MATCH, what is the 1 for? It's not working for me and I think
I understand everything but that, so I thought that might give me the answer.
Thanks for your help!!

"Peo Sjoblom" wrote:

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_shee t2=A2)*(Location_range_sheet2=B2),0))

entered with ctrl + shift & enter



Regards,


Peo Sjoblom



"Sunryzz" wrote:

Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

Sunryzz Wrote:
I keep getting N/A for the answer, but I can't figure out why. Here is
my
formula.

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=Sheet1 !A2)*(Sheet2!B1:B10=Sheet1!B2),0))

Try some formulas to see which part is failing. It appears that you
expect that somewhere on rows 1 through 10 fo sheet 2, both a cell in A
is going to match what is in A2 of sheet 1 and a cell in B (of the same
row) is going to match what is in B2 of sheet 1. So try 2 different
formulas. Let's say that the row that you think should match both A
and B is row 2. Try a formula that tests this. Like a formula in
sheet1:
=A2=Sheet2!A2 If this does not return true, there is your problem.
Check for things like trailing spaces.
and =B2=Sheet2!B2 should also return true. Also, as I'm sure was
mentioned in the original post as this is an array formula it MUST be
entered using Ctrl-Shift-Enter, not just Enter.


--
Kevin Vaughn
------------------------------------------------------------------------
Kevin Vaughn's Profile: http://www.excelforum.com/member.php...o&userid=34857
View this thread: http://www.excelforum.com/showthread...hreadid=546044