Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL should let me establish unlimited numbers of columns. | Excel Discussion (Misc queries) | |||
Lookup Two Columns - Again | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Lookup function skipping columns | Excel Worksheet Functions |