| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value based on contents of two cells
My list has 3 columns:
Column A Column B Column C County State Office Hall GA XYZ Office Fulton GA ABC Office Cuyahoga OH DEF Office Etc, for about 500 rows. I need to find the office that handles the county/state combo listed in Column C. Some county names are replicated in other states, so I need the one for the specific state. The reference cells a County will be in cell G1, and State in H1. These values are the result of a vlookup on another sheet. How do I do this, please? Either a VBA or the formula would be great. Thanks in advance. Robert |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding a value based on contents of two cells
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(COUNTA(G1:H1)<2,"",INDEX(C2:C4,MATCH(1,(A2:A4= G1)*(B2:B4=H1),0))) Biff "xjetjockey" wrote in message ups.com... My list has 3 columns: Column A Column B Column C County State Office Hall GA XYZ Office Fulton GA ABC Office Cuyahoga OH DEF Office Etc, for about 500 rows. I need to find the office that handles the county/state combo listed in Column C. Some county names are replicated in other states, so I need the one for the specific state. The reference cells a County will be in cell G1, and State in H1. These values are the result of a vlookup on another sheet. How do I do this, please? Either a VBA or the formula would be great. Thanks in advance. Robert |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding a value based on contents of two cells
=INDEX(Sheet1!C2:C500,MATCH(1,(Sheet1!A2:A500=Shee t2!$G$1)*(Sheet1!B2:B500=Sheet2!$H$1),0))
Adjust to suit ctrl+shift+enter (not just enter) "xjetjockey" wrote: My list has 3 columns: Column A Column B Column C County State Office Hall GA XYZ Office Fulton GA ABC Office Cuyahoga OH DEF Office Etc, for about 500 rows. I need to find the office that handles the county/state combo listed in Column C. Some county names are replicated in other states, so I need the one for the specific state. The reference cells a County will be in cell G1, and State in H1. These values are the result of a vlookup on another sheet. How do I do this, please? Either a VBA or the formula would be great. Thanks in advance. Robert |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding a value based on contents of two cells
Perfect. Thanks a million, and happy new year to ya.
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding a value based on contents of two cells
You're welcome. Thanks for the feedback!
Biff "xjetjockey" wrote in message oups.com... Perfect. Thanks a million, and happy new year to ya. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting cells based on conditional formatting | Excel Discussion (Misc queries) | |||
| Macro to highlight cells based on content | Excel Worksheet Functions | |||
| how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) | |||
| Automatically filling in cells based on another cell's content | Excel Worksheet Functions | |||
| Excel should have the option to merge contents when merging cells. | Excel Discussion (Misc queries) | |||
Linear Mode
