Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
HELP...... Please!
Data Set Example (Sheet2): A B C D E 1 EmpID Dept Site Start End 2 10 A Yellow May'07 3 500 B Green May'07 Jun'07 4 26 C Brown May'07 5 500 A Pink Jun'07 Step 1: I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept (B1 = A) and I need a formula that will give me the Site from the above in C1 on sheet1(Should = Pink). Step 2: However as you can see, the EmpID table above can have duplications and so I need the formula to look at both columns (in the row) at the same time and not match any entry in one column with any entry in the other column. NB: The date columns may need to be brought into play, but I would prefer the formula not to need to use these. Many thanks for any help possible. Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
Steven,
I way =INDEX(C1:C10,MATCH(D1&E1,A1:A10&B1:B10,0)) This looks for the 2 values held in D1 & E1 in cols A & B and returns the corresponding value in C. It's an array so enter with Ctrl+Shift+enter Mike "steven.holloway" wrote: HELP...... Please! Data Set Example (Sheet2): A B C D E 1 EmpID Dept Site Start End 2 10 A Yellow May'07 3 500 B Green May'07 Jun'07 4 26 C Brown May'07 5 500 A Pink Jun'07 Step 1: I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept (B1 = A) and I need a formula that will give me the Site from the above in C1 on sheet1(Should = Pink). Step 2: However as you can see, the EmpID table above can have duplications and so I need the formula to look at both columns (in the row) at the same time and not match any entry in one column with any entry in the other column. NB: The date columns may need to be brought into play, but I would prefer the formula not to need to use these. Many thanks for any help possible. Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
One way ..
In Sheet1, Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =INDEX(Sheet2!C$2:C$5,MATCH(1,(Sheet2!A$2:A$5=A1)* (Sheet2!B$2:B$5=B1),0)) Copy down as desired to return correspondingly for other pairs of inputs in cols A and B. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "steven.holloway" wrote: HELP...... Please! Data Set Example (Sheet2): A B C D E 1 EmpID Dept Site Start End 2 10 A Yellow May'07 3 500 B Green May'07 Jun'07 4 26 C Brown May'07 5 500 A Pink Jun'07 Step 1: I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept (B1 = A) and I need a formula that will give me the Site from the above in C1 on sheet1(Should = Pink). Step 2: However as you can see, the EmpID table above can have duplications and so I need the formula to look at both columns (in the row) at the same time and not match any entry in one column with any entry in the other column. NB: The date columns may need to be brought into play, but I would prefer the formula not to need to use these. Many thanks for any help possible. Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
Thanks Max, this works, but I have a 2 further questions;
1. Are there any issues with using these "array-entered" formulas? - For example, I have used this type of formula in the past and once I copied to a large area, the performance of the spreadsheet fade dramatically - and made the file pretty much unusable. 2. Can you add non "array-entered" formulas to an "array-entered" formula? - For example if I want to adapt the below formula to start with an "if(iserror(" statement? Many thanks again Steve "Max" wrote: One way .. In Sheet1, Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =INDEX(Sheet2!C$2:C$5,MATCH(1,(Sheet2!A$2:A$5=A1)* (Sheet2!B$2:B$5=B1),0)) Copy down as desired to return correspondingly for other pairs of inputs in cols A and B. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "steven.holloway" wrote: HELP...... Please! Data Set Example (Sheet2): A B C D E 1 EmpID Dept Site Start End 2 10 A Yellow May'07 3 500 B Green May'07 Jun'07 4 26 C Brown May'07 5 500 A Pink Jun'07 Step 1: I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept (B1 = A) and I need a formula that will give me the Site from the above in C1 on sheet1(Should = Pink). Step 2: However as you can see, the EmpID table above can have duplications and so I need the formula to look at both columns (in the row) at the same time and not match any entry in one column with any entry in the other column. NB: The date columns may need to be brought into play, but I would prefer the formula not to need to use these. Many thanks for any help possible. Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
"steven.holloway" wrote:
Thanks Max, this works, .. No prob. Glad it worked. but I have a 2 further questions; 1. Are there any issues with using these "array-entered" formulas? - For example, I have used this type of formula in the past and once I copied to a large area, the performance of the spreadsheet fade dramatically - and made the file pretty much unusable. Yes, that's a trade-off. I'd manage it by setting calc to manual mode, go about updating here and there w/o any fuss, then pressing F9 to calc only when I'm done with the updates, or whenever required. And as a non-array alternative, you could also use a helper col to concat the 2 cols A and B in Sheet2, then just do a normal INDEX/MATCH on similarly concatenated lookup values in Sheet1. 2. Can you add non "array-entered" formulas to an "array-entered" formula? - For example if I want to adapt the below formula to start with an "if(iserror(" statement? Yes, of course. But you would need to array-enter the combined formula. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
Many thanks again Max (Mike H your help was appreciated too)
Kind regards Steve "Max" wrote: "steven.holloway" wrote: Thanks Max, this works, .. No prob. Glad it worked. but I have a 2 further questions; 1. Are there any issues with using these "array-entered" formulas? - For example, I have used this type of formula in the past and once I copied to a large area, the performance of the spreadsheet fade dramatically - and made the file pretty much unusable. Yes, that's a trade-off. I'd manage it by setting calc to manual mode, go about updating here and there w/o any fuss, then pressing F9 to calc only when I'm done with the updates, or whenever required. And as a non-array alternative, you could also use a helper col to concat the 2 cols A and B in Sheet2, then just do a normal INDEX/MATCH on similarly concatenated lookup values in Sheet1. 2. Can you add non "array-entered" formulas to an "array-entered" formula? - For example if I want to adapt the below formula to start with an "if(iserror(" statement? Yes, of course. But you would need to array-enter the combined formula. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 Texts & Show a 3rd
welcome, Steve.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "steven.holloway" wrote in message ... Many thanks again Max (Mike H your help was appreciated too) Kind regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show True if numbers match | Excel Worksheet Functions | |||
how do i use if function to different texts | Excel Worksheet Functions | |||
summing texts | Excel Discussion (Misc queries) | |||
Combo Box Texts | Excel Discussion (Misc queries) | |||
trying to match more than 1 text value in a row to show end value | Excel Worksheet Functions |