Lookup two different columns
 

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!

Lookup two different columns
 

=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!

Lookup two different columns
 

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!

Lookup two different columns
 

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!

Lookup two different columns
 

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!

Lookup two different columns
 

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

Lookup two different columns
 

I think I've got it now. I was using Ctrl-Shift-Enter, but I thought you
were supposed to start with that instead of ending with it. It's not very
clear in the help file (or maybe I'm just a dope!!) I think it works now.
Thanks for your help!!

"Kevin Vaughn" wrote:


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

Lookup two different columns
 

OK, one more question (I hope). If the items in my columns are text instead
of numbers, does that make a difference? So, my Line column is Air Piping,
Frame, Main, etc? It was working, but now I'm trying to use it for a text
version of the same thing and it is coming back with N/A again. GRRRRR!

"Kevin Vaughn" wrote:


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

Lookup two different columns
 

Text should work (just look out for trailing spaces etc.)
--
Kevin Vaughn


"Sunryzz" wrote:

OK, one more question (I hope). If the items in my columns are text instead
of numbers, does that make a difference? So, my Line column is Air Piping,
Frame, Main, etc? It was working, but now I'm trying to use it for a text
version of the same thing and it is coming back with N/A again. GRRRRR!

"Kevin Vaughn" wrote:


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

Lookup two different columns
 

You are very correct. I found out the reason for my N/A's this time was a
leading space in one of the columns.

Thanks again for all your help!!

"Kevin Vaughn" wrote:

Text should work (just look out for trailing spaces etc.)
--
Kevin Vaughn


"Sunryzz" wrote:

OK, one more question (I hope). If the items in my columns are text instead
of numbers, does that make a difference? So, my Line column is Air Piping,
Frame, Main, etc? It was working, but now I'm trying to use it for a text
version of the same thing and it is coming back with N/A again. GRRRRR!

"Kevin Vaughn" wrote:


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

Lookup two different columns
 

You're welcome! Glad you got it working.
--
Kevin Vaughn


"Sunryzz" wrote:

You are very correct. I found out the reason for my N/A's this time was a
leading space in one of the columns.

Thanks again for all your help!!

"Kevin Vaughn" wrote:

Text should work (just look out for trailing spaces etc.)
--
Kevin Vaughn


"Sunryzz" wrote:

OK, one more question (I hope). If the items in my columns are text instead
of numbers, does that make a difference? So, my Line column is Air Piping,
Frame, Main, etc? It was working, but now I'm trying to use it for a text
version of the same thing and it is coming back with N/A again. GRRRRR!

"Kevin Vaughn" wrote:


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