Formula help

[Jan]

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 don’t 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

 

[Max]

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]

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]

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