Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
Dear Excel geniuses,
I have two excel worksheets. One with data which is arranged in columns and one where I want to pull the data into which should be arranged in rows. I need to find a value based on 3 criteria and for that reason I use the INDEX and MATCH formulas, however my excel knowledge abandons me here so I was hoping for your assistance. In order to clarify I have made below example: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7; 0))} The formula should return 10 but it gives a #N/A. Because of the lay-out of the data I have, I am not very flexible with arranging the data differently so I can use another solution. I have pasted the example straight out of Excel so I hope it is still readible. Do you have any idea? Thnks! RMF |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
Just wondering how you can expect a value of 10 when 10 is not even part of
your lookup array (which is C5:E5)? "RMF" wrote: Dear Excel geniuses, I have two excel worksheets. One with data which is arranged in columns and one where I want to pull the data into which should be arranged in rows. I need to find a value based on 3 criteria and for that reason I use the INDEX and MATCH formulas, however my excel knowledge abandons me here so I was hoping for your assistance. In order to clarify I have made below example: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7; 0))} The formula should return 10 but it gives a #N/A. Because of the lay-out of the data I have, I am not very flexible with arranging the data differently so I can use another solution. I have pasted the example straight out of Excel so I hope it is still readible. Do you have any idea? Thnks! RMF |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
You are right. sorry. It got mixed up when pasting in from excel into a post.
The corrected version below: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(B4:D4;MATCH(A9&B9&C9;B1:B3&C1:C3&D1:D3;0)) } Thnks, R ----------------------------------------- "FiluDlidu" wrote: Just wondering how you can expect a value of 10 when 10 is not even part of your lookup array (which is C5:E5)? "RMF" wrote: Dear Excel geniuses, I have two excel worksheets. One with data which is arranged in columns and one where I want to pull the data into which should be arranged in rows. I need to find a value based on 3 criteria and for that reason I use the INDEX and MATCH formulas, however my excel knowledge abandons me here so I was hoping for your assistance. In order to clarify I have made below example: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7; 0))} The formula should return 10 but it gives a #N/A. Because of the lay-out of the data I have, I am not very flexible with arranging the data differently so I can use another solution. I have pasted the example straight out of Excel so I hope it is still readible. Do you have any idea? Thnks! RMF |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
Try the following formulas which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... D9, copied down: =INDEX($B$4:$D$4,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) E9, copied down: =INDEX($B$5:$D$5,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) F9, copied down: =INDEX($B$6:$D$6,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) Or, it can be done using one formula... D9, copied down and across: =INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9),0)) Hope this helps! In article , RMF wrote: You are right. sorry. It got mixed up when pasting in from excel into a post. The corrected version below: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(B4:D4;MATCH(A9&B9&C9;B1:B3&C1:C3&D1:D3;0)) } Thnks, R ----------------------------------------- "FiluDlidu" wrote: Just wondering how you can expect a value of 10 when 10 is not even part of your lookup array (which is C5:E5)? "RMF" wrote: Dear Excel geniuses, I have two excel worksheets. One with data which is arranged in columns and one where I want to pull the data into which should be arranged in rows. I need to find a value based on 3 criteria and for that reason I use the INDEX and MATCH formulas, however my excel knowledge abandons me here so I was hoping for your assistance. In order to clarify I have made below example: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7; 0))} The formula should return 10 but it gives a #N/A. Because of the lay-out of the data I have, I am not very flexible with arranging the data differently so I can use another solution. I have pasted the example straight out of Excel so I hope it is still readible. Do you have any idea? Thnks! RMF |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
Excellent, many thanks!
One question regarding this formula: =INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$2=$B9)*($B$3:$D$3=$C9),0)) Why is the lookup value in the second MATCH formula 1? Dont understand this one, perhaps I am missing something here Thnks! RMF ========== "Domenic" wrote: Try the following formulas which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... D9, copied down: =INDEX($B$4:$D$4,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) E9, copied down: =INDEX($B$5:$D$5,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) F9, copied down: =INDEX($B$6:$D$6,MATCH(1,($B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9), 0)) Or, it can be done using one formula... D9, copied down and across: =INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$ 2=$B9)*($B$3:$D$3=$C9),0)) Hope this helps! In article , RMF wrote: You are right. sorry. It got mixed up when pasting in from excel into a post. The corrected version below: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(B4:D4;MATCH(A9&B9&C9;B1:B3&C1:C3&D1:D3;0)) } Thnks, R ----------------------------------------- "FiluDlidu" wrote: Just wondering how you can expect a value of 10 when 10 is not even part of your lookup array (which is C5:E5)? "RMF" wrote: Dear Excel geniuses, I have two excel worksheets. One with data which is arranged in columns and one where I want to pull the data into which should be arranged in rows. I need to find a value based on 3 criteria and for that reason I use the INDEX and MATCH formulas, however my excel knowledge abandons me here so I was hoping for your assistance. In order to clarify I have made below example: A B C D E F 1 Red Orange Green 2 Blue Purple White 3 Yellow Black Pink 4 Small 10 11 5 5 Medium 12 7 4 6 Large 6 3 2 7 8 Small Medium Large 9 Red Blue Yellow #N/A 10 Orange Purple Black 11 Green White Pink The formula I use in cell D9 is {=INDEX(C5:E5;MATCH(B10&C10&D10;C2:C7&D2:D7&E2:E7; 0))} The formula should return 10 but it gives a #N/A. Because of the lay-out of the data I have, I am not very flexible with arranging the data differently so I can use another solution. I have pasted the example straight out of Excel so I hope it is still readible. Do you have any idea? Thnks! RMF |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX and MATCH to find data in 2 different sheets
In article ,
RMF wrote: Excellent, many thanks! You're very welcome! Glad I could help! One question regarding this formula: =INDEX($B$4:$D$6,MATCH(D$8,$A$4:$A$6,0),MATCH(1,($ B$1:$D$1=$A9)*($B$2:$D$2=$B9 )*($B$3:$D$3=$C9),0)) Why is the lookup value in the second MATCH formula 1? Dont understand this one, perhaps I am missing something here In the second MATCH function, you'll notice that there are three conditional statements. Each statement returns an array of TRUE and/or FALSE as each element in the array is evaluated... ($B$1:$D$1=$A9) evaluates to: {TRUE,FALSE,FALSE} ($B$2:$D$2=$B9) evaluates to: {TRUE,FALSE,FALSE} ($B$3:$D$3=$C9) evaluates to: {TRUE,FALSE,FALSE} These arrays are then multiplied together... ($B$1:$D$1=$A9)*($B$2:$D$2=$B9)*($B$3:$D$3=$C9) which evaluates to: {TRUE,FALSE,FALSE}*{TRUE,FALSE,FALSE}*{TRUE,FALSE, FALSE} and gives us: {1,0,0} Notice that the numerical equivalent of TRUE/FALSE is 1/0, respectively. Also, you'll notice that the statements evaluate to an array of 1's and 0's. With the lookup value for the MATCH function being 1, MATCH returns 1,which is used as an argument for the INDEX function. Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
Data Validation w/ If, Match & Index Statements | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |