vlookup with index match ... is this possible?

[albert28]

Hi Guys,

i don't know if i can mix the vlookup and index match into one formula but i dont know how to do it.

can anyone here know how to do this? please help me.

i have attached a dummy file that i need to look the value from Data tab and from summary it will mark it as X if found in data tab.



thanks in advance!

[Pete_UK]

Put this formula in B3 of the Summary sheet:

=IF(COUNTIF(INDEX(Data!$B$2:$L$21,MATCH($A3,Data!$A$2:$A$21,0),0),B$1)>0,"x","")

Then copy that cell's formula into E3 and into H3.

Then put this formula in C3:

=IF(B3="x","x","")

and copy that into cells D3, F3, G3 and I3. Then copy B3:I3 down to row 22.

Hope this helps.

Pete

[albert28]

Thanks, Pete_UK.

But it mark "X" all of the fields in summary tab.

i.e report 1 should have a mark X in Sample 1 and 2 only.

[Pete_UK]

The attached file contains the formulae.

Hope this helps.

Pete