conditional vlookup formula

[julesl]

Hi

I have a spreadsheet containing 2 worksheets and I need to copy data into the second worksheet based on certain criteria.

The formula I am trying to write is as follows:-

Look up A2(id) from Worksheet 2 in Worksheet 1 (A2:N691) and bring back the contents of cell I2 if K2 = "Main" (in worksheet 1)otherwise bring back nothing.

Can anyone help me please?

Thanks

Jules

 

[SkipVought]

Jules,

on sheet 2
[tt]
=if(Sheet1!k2="Main","",IF(ISERROR(MATCH(A2,Sheet1!$A$2:$A$691,0)),"",INDEX(Sheet1!$A$2:$N$691,MATCH(A2,Sheet1!$A$2:$A$691,0),2)))
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[julesl]

Hi Skip

Thank you for your reply.

Having tried your formula I realised my original description of what I'm trying to do is too ridgid.

I think it should say:-

Look up A2(id) from Worksheet 2 in Worksheet 1 (A2:N691) and bring back the contents of cell I* (which is a date of birth) if K* = "Main" (in worksheet 1)otherwise bring back nothing, where * is the row where the match occurs between ID and "Main".

Thanks

Jules

 

[01Patrik]

Skip,

Is there a reason why you prefer the use of nested match / index functions instead of Vlookup in this particular example?

I know that the nested solution is preferred if one needs data on the left of the lookup values, but if not?


// Patrik

 

[SkipVought]

I nearly always use Match 'n' Index. Just prefer that approch. Mors specifically, I almost always use NAMED RANGES obtaine using Insert/Name/Create - Create names in top row. Then the expression becomes more like
[tt]
=IF(ISERROR(MATCH(A2,Name,0)),"",INDEX(Address,MATCH(A2,Name,1))
=IF(ISERROR(MATCH(A2,Name,0)),"",INDEX(ZipCode,MATCH(A2,Name,1))
...
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}