Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
I have 2 lists in separate workbooks. The lists do not have the same number
of rows. I have been trying to create a formula to achieve a result, but have been unsuccessful so I dont know if what I am trying to do is possible. Below is what I want to accomplish. On List1 I need a formula that will return a value from List2. I want to enter the formula in List1_Column R. If the value in List1ColumnQ = the value in List2ColumnN and the value in List2ColumnF=y, return the value in List2ColumnO . Naturally, the List2 values columnN, columnF & colmunO must be same row. Any help with this formula will be greatly appreciated. If I am unclear on what I want to achieve, let me know so that I can hopefully clarify. TIA Jan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
Indicatively, a dual criteria, array-entered* index/match would do it
In List1, you would place something like this in say, R1, array-entered*: =INDEX(List2ColO,MATCH(1,(List2ColN=Q1)*(List2ColF ="y"),0)) Then copy R1 down *To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing ENTER) All ranges need to be identically sized, and in my xl03, ranges cannot be entire col ranges, ie ranges have to be for example: List2!$O$1:$O$100, List2!$F$1:$F$100 -- Max Singapore http://savefile.com/projects/236895 Downloads:16,000 Files:354 Subscribers:53 xdemechanik --- "Jan" wrote: I have 2 lists in separate workbooks. The lists do not have the same number of rows. I have been trying to create a formula to achieve a result, but have been unsuccessful so I dont know if what I am trying to do is possible. Below is what I want to accomplish. On List1 I need a formula that will return a value from List2. I want to enter the formula in List1_Column R. If the value in List1ColumnQ = the value in List2ColumnN and the value in List2ColumnF=y, return the value in List2ColumnO . Naturally, the List2 values columnN, columnF & colmunO must be same row. Any help with this formula will be greatly appreciated. If I am unclear on what I want to achieve, let me know so that I can hopefully clarify. TIA Jan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
Hi Max,
I entered the array formula on List1 as suggested and it returns #N/A in all cells. I've separated formula below to make it easier to read. Any other thoughts? {=INDEX('[Koda.xls]Sheet1'!$O$2:$O$300, MATCH(1,('[Koda.xls]Sheet1'!$N$2:$N$300=Q11)* ('[Koda.xls]Sheet1'!$F$2:$F$300="y"),0))} TIA "Max" wrote: Indicatively, a dual criteria, array-entered* index/match would do it In List1, you would place something like this in say, R1, array-entered*: =INDEX(List2ColO,MATCH(1,(List2ColN=Q1)*(List2ColF ="y"),0)) Then copy R1 down *To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing ENTER) All ranges need to be identically sized, and in my xl03, ranges cannot be entire col ranges, ie ranges have to be for example: List2!$O$1:$O$100, List2!$F$1:$F$100 -- Max Singapore http://savefile.com/projects/236895 Downloads:16,000 Files:354 Subscribers:53 xdemechanik --- "Jan" wrote: I have 2 lists in separate workbooks. The lists do not have the same number of rows. I have been trying to create a formula to achieve a result, but have been unsuccessful so I dont know if what I am trying to do is possible. Below is what I want to accomplish. On List1 I need a formula that will return a value from List2. I want to enter the formula in List1_Column R. If the value in List1ColumnQ = the value in List2ColumnN and the value in List2ColumnF=y, return the value in List2ColumnO . Naturally, the List2 values columnN, columnF & colmunO must be same row. Any help with this formula will be greatly appreciated. If I am unclear on what I want to achieve, let me know so that I can hopefully clarify. TIA Jan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
Looks ok when I tested your formula here. It could be data inconsistencies
throwing correct matching off, eg: extraneous white spaces here and there. Try it with TRIM wrapped for more robust matching, array-entered: =INDEX([Koda.xls]Sheet1!$O$2:$O$300, MATCH(1,(TRIM([Koda.xls]Sheet1!$N$2:$N$300)=TRIM(Q11))* (TRIM([Koda.xls]Sheet1!$F$2:$F$300)="y"),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,000 Files:354 Subscribers:53 xdemechanik --- "Jan" wrote: Hi Max, I entered the array formula on List1 as suggested and it returns #N/A in all cells. I've separated formula below to make it easier to read. Any other thoughts? {=INDEX('[Koda.xls]Sheet1'!$O$2:$O$300, MATCH(1,('[Koda.xls]Sheet1'!$N$2:$N$300=Q11)* ('[Koda.xls]Sheet1'!$F$2:$F$300="y"),0))} TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|