Index, match, or hlookup ~ which function to use @_@

P

PJS

Hi ^_^

I was wondering how to achieve this. Any help or input would be great.
Thank you.

Sheet1 ~ data source
Type/Value
A 1 2 3
B 2 4 6
C 1 3 6

Sheet2
1 2 3 4 5 6
A Y Y Y
B Y Y Y
C Y Y Y
 
J

JMB

One way, entered in B2 of Sheet2 (entered w/Cntrl+Shift+Enter):

=IF(OR(VLOOKUP($A2,Sheet1!$A$1:$D$3,{2,3,4},0)=B$1),"Y","")
 
T

T. Valko

With this data on Sheet1 in the range A2:D4
A 1 2 3
B 2 4 6
C 1 3 6
Click to expand...

Sheet2 B1:G1 = 1,2,3,4,5,6
Sheet2 A2:A4 = A, B, C

Enter this formula on Sheet2 in cell B2:

=IF(ISNUMBER(MATCH(B$1,INDEX(Sheet1!$B$2:$D$4,MATCH($A2,Sheet1!$A$2:$A$4,0),),0)),"Y","")

Copy across to G2 then down to row 4.

Biff
 
Top