Lookup two different columns

Sunryzz · 05-26-2006, 04:45 PM

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!

Peo Sjoblom · 05-26-2006, 05:10 PM

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_sheet2=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 · 05-26-2006, 06:10 PM

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

Kevin Vaughn · 05-29-2006, 04:10 PM

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_sheet2=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 · 05-30-2006, 03:40 PM

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

**Kevin Vaughn** · 05-30-2006, 04:56 PM

[QUOTE=Sunryzz]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.

Sunryzz · 05-30-2006, 05:20 PM

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

Sunryzz · 05-30-2006, 05:30 PM

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

Kevin Vaughn · 05-30-2006, 06:20 PM

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

Sunryzz · 05-31-2006, 10:30 AM

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

Kevin Vaughn · 05-31-2006, 01:15 PM

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