Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
I have been struggling with how to solve this problem. Sounds simple
verbally but I can't quite get it! I need a formula that will look at 3 columns in an array - if the value in column A and the value in column B are the same as the values in adjacent columns in a spreadsheet, then delibet the value in column C? E.g.; A B C 1 LEVEL1 LEVEL2_OLD LEVEL2_NEW 2 FINANCE INVEST INVEST1 3 ENGINEERING BUILD BUILD2 4 QUALITY CHECK CHECK1 5 PROPERTY INVEST INVEST2 6 RESEARCH CHECK CHECK2 7 8 ENGINEERING BUILD This might be the array - the values in A8, B8 might be Engineering and Build - I need to deliver BUILD2. I hope this problem isn't as much of a tease for your minds as it is for mine. Thanks in advance for any suggestions. -- JT Sydney, Australia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
One way....
=INDEX(C1:C6,MATCH(1,(A1:A6=A8)*(B1:B6=B8),0)) confirmed with CTRL+SHIFT+ENTER "JT" wrote: I have been struggling with how to solve this problem. Sounds simple verbally but I can't quite get it! I need a formula that will look at 3 columns in an array - if the value in column A and the value in column B are the same as the values in adjacent columns in a spreadsheet, then delibet the value in column C? E.g.; A B C 1 LEVEL1 LEVEL2_OLD LEVEL2_NEW 2 FINANCE INVEST INVEST1 3 ENGINEERING BUILD BUILD2 4 QUALITY CHECK CHECK1 5 PROPERTY INVEST INVEST2 6 RESEARCH CHECK CHECK2 7 8 ENGINEERING BUILD This might be the array - the values in A8, B8 might be Engineering and Build - I need to deliver BUILD2. I hope this problem isn't as much of a tease for your minds as it is for mine. Thanks in advance for any suggestions. -- JT Sydney, Australia |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
Thanks Daddylonglegs - elegant and solved my problem...
-- JT Sydney, Australia "daddylonglegs" wrote: One way.... =INDEX(C1:C6,MATCH(1,(A1:A6=A8)*(B1:B6=B8),0)) confirmed with CTRL+SHIFT+ENTER "JT" wrote: I have been struggling with how to solve this problem. Sounds simple verbally but I can't quite get it! I need a formula that will look at 3 columns in an array - if the value in column A and the value in column B are the same as the values in adjacent columns in a spreadsheet, then delibet the value in column C? E.g.; A B C 1 LEVEL1 LEVEL2_OLD LEVEL2_NEW 2 FINANCE INVEST INVEST1 3 ENGINEERING BUILD BUILD2 4 QUALITY CHECK CHECK1 5 PROPERTY INVEST INVEST2 6 RESEARCH CHECK CHECK2 7 8 ENGINEERING BUILD This might be the array - the values in A8, B8 might be Engineering and Build - I need to deliver BUILD2. I hope this problem isn't as much of a tease for your minds as it is for mine. Thanks in advance for any suggestions. -- JT Sydney, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match lookup | New Users to Excel | |||
index?lookup?match?if? | New Users to Excel | |||
lookup/index/match - help! | Excel Discussion (Misc queries) | |||
Lookup/Index/Match HELP! | Excel Discussion (Misc queries) | |||
index / match /lookup ? help | Excel Worksheet Functions |