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

 

[Robert Mika]

hi MrC and welcome to the board:
Try formula from this post:

http://www.mrexcel.com/forum/showthread.php?p=2263339#post2263339

 

[MrRC]

Thank you

I've already tried this, the problem is when the for example bla has A, D, E

When i drag the formula and the sheet have A B C D E F, it will only put A, D, E in the first 3 columns..

What i can't do is some formula to check first with A B C D E F and then with the other sheet

Any suggestions are welcome

 

[T. Valko]

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

Try this...

Book1
	A	B
2	Bla	C
3	Ble	B
4	Blu	A
5	Bla	A
6	Ble	C
Sheet2

Book1
	A	B	C	D
1	_	A	B	C
2	Bla	X	_	X
3	Ble	_	X	X
Sheet1

This formula entered in B2:

=IF(COUNTIFS(Sheet2!$A$2:$A$6,$A2,Sheet2!$B$2:$B$6,B$1),"X","")

Copy across then down as needed.

 

[MrRC]

Awesome!

It's working, thanks man!

If i want to have on Sheet 2 one more column, Letter 2 and to do the same, what i have to do? Just add one more clause to the countif?

 

[T. Valko]

Awesome!

It's working, thanks man!

If i want to have on Sheet 2 one more column, Letter 2 and to do the same, what i have to do? Just add one more clause to the countif?

Not sure what you mean?

 

[MrRC]

In the Sheet2 instead of two columns, i want to put a third one..

<table class="tborder" align="center" border="0" cellpadding="6" cellspacing="1" width="100%"><tbody><tr title="Post 2754489" valign="top"><tr title="Post 2754489" valign="top"><td class="alt1">Sheet2:
Name Letter1 Letter 2
bla__ A______D
ble__ A______E
bla__ B______F

</td></tr></tr></tbody></table>

How can i change the formula to check for values in this new third column?

 

[T. Valko]

In the Sheet2 instead of two columns, i want to put a third one..

<TABLE class=tborder cellSpacing=1 cellPadding=6 width="100%" align=center border=0><TBODY><TR title="Post 2754489" vAlign=top><TR title="Post 2754489" vAlign=top><TD class=alt1>Sheet2:
Name Letter1 Letter 2
bla__ A______D
ble__ A______E
bla__ B______F


</TD></TR></TR></TBODY></TABLE>

How can i change the formula to check for values in this new third column?

We'd need a different formula.

Something like this:

=IF(SUMPRODUCT((Sheet2!$A$2:$A$4=$A2)*(Sheet2!$B$2:$C$4=B$1)),"X","")

 

[MrRC]

Hey T. Valko,

=IF(SUMPRODUCT((Contactos!$CE$2:$CE$2444=$CE2)*(Contactos!$CI$2:$CJ$2444=AP$1));"X";""), this doesn't work..

The original I'm using is this =IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE3;Contactos!$CI$2:$CI$2445;AP$1);"X";"") and works very good..

Any suggestions?

 

[MrRC]

It's working <table border="0" cellpadding="0" cellspacing="0" width="64"><tr height="20"><td style="height:15.0pt;width:48pt" width="64" height="20">
</td></tr></table>=IF(SUMPRODUCT((Contactos!$CE$2:$CE$2445=Contactos!$CE65)*(Contactos!$CI$2:$CJ$2445=AL$1));"X";"")

However if i want to add a third "check" with this formula how can I do it ?

With Countif i just added a new criteria..

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE62;Contactos!$CG$2:$CG$2445;Contactos!$CG62;Contactos!$CI$2:$CI$2445;AM$1);"X";"")

Thanks for all the help!

<table border="0" cellpadding="0" cellspacing="0" width="64"><col width="64"><tr height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td></tr></table>