| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking up values in 1 column based on 2 other columns
Hi,
I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking up values in 1 column based on 2 other columns
Try entered with Ctrl+Shift+Enter:
=INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0)) HTH "CMO" wrote: Hi, I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking up values in 1 column based on 2 other columns
Thanks, I'm still getting #N/A, are there any limitations on the INDEX
function? As the result will be text. "Toppers" wrote: Try entered with Ctrl+Shift+Enter: =INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0)) HTH "CMO" wrote: Hi, I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking up values in 1 column based on 2 other columns
It works OK for me; my sample data .... A2=21,F2=01/08/2006,
C10=21,C21=01/08/2006 Are you sure all the data is the correct format e.g. not comparing numbers with text? "CMO" wrote: Thanks, I'm still getting #N/A, are there any limitations on the INDEX function? As the result will be text. "Toppers" wrote: Try entered with Ctrl+Shift+Enter: =INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0)) HTH "CMO" wrote: Hi, I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking up values in 1 column based on 2 other columns
User error!!
Thanks, works spot on. Chris "Toppers" wrote: It works OK for me; my sample data .... A2=21,F2=01/08/2006, C10=21,C21=01/08/2006 Are you sure all the data is the correct format e.g. not comparing numbers with text? "CMO" wrote: Thanks, I'm still getting #N/A, are there any limitations on the INDEX function? As the result will be text. "Toppers" wrote: Try entered with Ctrl+Shift+Enter: =INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0)) HTH "CMO" wrote: Hi, I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking up values in 1 column based on 2 other columns
Good! and thank you for the feedback.
"CMO" wrote: User error!! Thanks, works spot on. Chris "Toppers" wrote: It works OK for me; my sample data .... A2=21,F2=01/08/2006, C10=21,C21=01/08/2006 Are you sure all the data is the correct format e.g. not comparing numbers with text? "CMO" wrote: Thanks, I'm still getting #N/A, are there any limitations on the INDEX function? As the result will be text. "Toppers" wrote: Try entered with Ctrl+Shift+Enter: =INDEX(Amendments!$G$2:$G$10000,MATCH(1,($C$10=Ame ndments!$A$2:$A$10000)*(C21=Amendments!$F$2:$F$100 00),0)) HTH "CMO" wrote: Hi, I've been trying to use the INDEX function to lookup values in 1 column based on 2 others. The formula I have is below, but the result I'm getting is "#N/A". I have confirmed the formula using Ctrl+Shift+Enter, but that doesn't seem to have helped. Formula - =INDEX(Amendments!$G$2:$G$10000,MATCH($C$10&$C$21, Amendments!$A$2:$A$10000&Amendments!$F$2:$F$10000, 0)) C10 is a field containing a number and C21 is a field containing a date. All help will be much appreciated! Thx Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match 2 databases based on a column with same values | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| sumproduct 2 columns based on criteria in 3rd column | Excel Discussion (Misc queries) | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Linear Mode
