If statement with vlookup

[albert28]

Hi

I have attached the sample template and i have a hard time on thinking if there is a way of doing a vlookup and at the same this with IF nested statement to find values.

from the summary tab i just need to enter ID number based on the data tab. and the details will appear with their names and campaigns.

Hope got your response on this one.

Thanks alot.

[Fotis1991]

Try in D9 and copy down this ARRAY formula.

=IFERROR(INDEX(data!$D$6:$D$100,SMALL(IF(data!$C$6:$C$100=$D$6,ROW(data!$D$6:$D$100)-5),ROW(data!D1))),"")

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[albert28]

thanks Fotis 1991, it works actually but i have Not in list"values from my data and i don't want to see that NOT in LIST value.

Can you please help me on this.

[Fotis1991]

thanks Fotis 1991, it works actually but i have Not in list"values from my data and i don't want to see that NOT in LIST value.

Can you please help me on this.

Can you pls explain this a little more? I am not able to understand what you mean.

[albert28]

it is now okay.

but this formula =IFERROR(INDEX(data!$D$6:$D$100,SMALL(IF(data!$C$6:$C$100=$D$6,ROW(data!$D$6:$D$100)-5),ROW(data!D1))),"")

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.

Won't worked in Excel 2003 version.

I don't know what to do?

[Fotis1991]

IFERROR does not works in Excel versions<2007. You profile says that you use Excel2007 and you uploaded a xlsx excel document.

In any case for ALL versions try this..

=IF(ISERROR(INDEX(data!$D$6:$D$100,SMALL(IF(data!$C$6:$C$100=$D$6,ROW(data!$D$6:$D$100)-5),ROW(data!D1)))),"",INDEX(data!$D$6:$D$100,SMALL(IF(data!$C$6:$C$100=$D$6,ROW(data!$D$6:$D$100)-5),ROW(data!D1))))

[albert28]

thanks its working now with 2003 version on my officemate desktop

thank you so much

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

[Fotis1991]

Answering to your PM, i can not understand why when you reopen your pc formula does not work.

Something wrong with copy paste? I attach a sample worksheet.

Also N0. I saw the thread that you started in VBA section but I don't believe that you need VBA for this. If you want to do using VBA it's up to you.

http://www.excelforum.com/excel-gene...vba-codes.html