VLOOKUP / INDEX Problem

[MrRC]

Dear sirs,

I'm having some problems with an excel formula and i would like to hear your expert opinions on this
I'm using Excel 2007 and I have 2 Sheets:
Sheet1:
___A B C D E F
bla X X
ble X

Sheet2:
Name Letter
bla__ A
ble__ A
bla__ B

How can i make a formula to populate Sheet1 with an X the letters that it finds on the contact auto?

I'm using =IF(INDEX(Contactos!$CI2:$CI$2445;SMALL(IF(Contactos!$CE2=Contactos!$CE2:$CE$2445;ROW(Contactos!$CE2:$CE$2445)-MIN(ROW(Contactos!$CE2:$CE$2445))+1; ""); ROW($A$1)))=AP$1;"X";"") it works, but only finds the first item.. i'm going the wrong way

Thanks for your help

 

[T. Valko]

Ok here's an example

Contactos Sheet
<TABLE cellSpacing=0 cellPadding=0 width=876 border=0><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" span=2 width=203><COL style="WIDTH: 209pt; mso-width-source: userset; mso-width-alt: 10166" width=278><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 152pt; HEIGHT: 15pt" width=203 height=20>Nome Completo</TD><TD style="WIDTH: 209pt" width=278>Partner</TD><TD style="WIDTH: 48pt" width=64>Interest 1</TD><TD style="WIDTH: 48pt" width=64>Interest 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name1</TD><TD class=xl117>P1</TD><TD class=xl117>CRM</TD><TD class=xl117 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name2</TD><TD class=xl117>P2

</TD><TD class=xl117>SOA</TD><TD class=xl117 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name3</TD><TD class=xl117>P2

</TD><TD class=xl117>CRM</TD><TD class=xl117 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name4</TD><TD class=xl117>P3

</TD><TD class=xl117>CRM</TD><TD class=xl117 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name5</TD><TD class=xl117>P4

</TD><TD class=xl117>ERP</TD><TD class=xl117 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl117 style="HEIGHT: 15pt" height=20>Name6</TD><TD class=xl117>P5

</TD><TD class=xl117>ERP</TD><TD class=xl117 align=right>HCM

</TD></TR></TBODY></TABLE>
This is the main

<TABLE cellSpacing=0 cellPadding=0 width=1377 border=0><COLGROUP><COL style="WIDTH: 48pt" span=12 width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 49.5pt; mso-height-source: userset" height=66><TD class=xl117 style="WIDTH: 48pt; HEIGHT: 49.5pt" width=64 height=66>

</TD><TD class=xl123 style="WIDTH: 48pt" width=64>Perf. & Avail.</TD><TD class=xl123 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>Storage & DW</TD><TD class=xl123 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>Security</TD><TD class=xl124 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>SAM</TD><TD class=xl125 style="WIDTH: 48pt" width=64>SOA</TD><TD class=xl125 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>E2.0</TD><TD class=xl125 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>BI</TD><TD class=xl126 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>IDM</TD><TD class=xl127 style="WIDTH: 73pt" width=97>ERP</TD><TD class=xl127 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>EPM</TD><TD class=xl127 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>CRM</TD><TD class=xl127 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>CRM OD</TD><TD class=xl127 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>HCM</TD><TD class=xl127 style="BORDER-LEFT: medium none; WIDTH: 48pt" width=64>BI Apps</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name1

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name2</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name3</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name4</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name5</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Name6</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>X

</TD><TD>

</TD></TR></TBODY></TABLE>
It should check Name and Partner (because sometimes the names are repeated in differente partners) then it should check for interest 1 and 2..

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;CE2;Contactos!$CG$2:$CG$2445;$C4;Contactos!$CI$2:$CI$2445; AP$1);"X";"")

Does this very, but only checks Interest 1..

Thanks man

Ok, let's assume you table data is in the range A2:D7.

Your summary area is:

Names listed in the range A13:A18.
Column headers in the range B12:O12.

Enter this formula in B13:

=IF(SUMPRODUCT(--($A$2:$A$7=$A13),--(($C$2:$C$7=B$12)+($D$2:$D$7=B$12)>0)),"X","")

Copy across to O13 then down to B18:O18.

 

[MrRC]

Weird.. doesn't work, here we use ";" instead of ","..

Contactos!$CE$2:$CE$2445, it's my name range
Contactos!$CI$2:$CI$2455, it's my interest 1 range
Contactos!$CJ$2:$CJ$2445, it's my interest 2 range

$AF1-AV1 it's the interests range..

=IF(SUMPRODUCT(--(Contactos!$CE$2:$CE$2445=A2);--((Contactos!$CI$2:$CI$2455=$AF1)+(Contactos!$CJ$2:$CJ$2445=$AF1)>0));"X";"")

In this formula it only checks Name and not partner..

I really appreciate your time on this subject..

 

[T. Valko]

Weird.. doesn't work, here we use ";" instead of ","..

Contactos!$CE$2:$CE$2445, it's my name range
Contactos!$CI$2:$CI$2455, it's my interest 1 range
Contactos!$CJ$2:$CJ$2445, it's my interest 2 range

$AF1-AV1 it's the interests range..

=IF(SUMPRODUCT(--(Contactos!$CE$2:$CE$2445=A2);--((Contactos!$CI$2:$CI$2455=$AF1)+(Contactos!$CJ$2:$CJ$2445=$AF1)>0));"X";"")

In this formula it only checks Name and not partner..

I really appreciate your time on this subject..

What does the partner have to do with it?

The last formula I suggested places the X's in the same cells as is displayed in your reply post #20.

Would you like me to put together a sample file that demonstrates this?

 

[MrRC]

If you dont mind.

In the main sheet, before Name i have partner too (Because some names repeat themselves and i have to check partner to differentiate them)

 

[T. Valko]

If you dont mind.

In the main sheet, before Name i have partner too (Because some names repeat themselves and i have to check partner to differentiate them)

Here's a small sample file.

zzzMrRC.xlsx 10kb

http://cjoint.com/?AFvqasq97wb

 

[MrRC]

This work but maybe you didnt understand what i explain.. how can i a check to when it checks for the interests 1 and 2 also checks name and partner?

When we finish this you will send me your paypal number to i pay u a beer!

 

[T. Valko]

This work but maybe you didnt understand what i explain.. how can i a check to when it checks for the interests 1 and 2 also checks name and partner?

When we finish this you will send me your paypal number to i pay u a beer!

I guess I don't understand what you want!

What does the partner have to do with it? You haven't explained that.

 

[MrRC]

In the excel you send me, just add "Partner" in B12 before SAM and the formula should check if Name1 is P1, there could be situations where Name1 is P2 for example..

 

[T. Valko]

In the excel you send me, just add "Partner" in B12 before SAM and the formula should check if Name1 is P1, there could be situations where Name1 is P2 for example..

Well, at this point I'd need to see the actual file to figure this out.

Can you put together a SMALL sample file that shows your setup and what results you expect?

A SMALL file! Don't need 100's or 1000's of rows of data, just 10 or 20 rows and a few columns will do.

You can use a free file hosting site if need be.

 

[MrRC]

Ok, here it is..

http://cjoint.com/data3/3Fwkh3kvnVV.htm