How to use Index and Match to lookup headers in row and column?
=INDEX($A$1:$F$15,MATCH("A",$A$1:$A$15,0),MATCH(1, $A$1:$F$1,0))
For the duplicates, select a range of cells, say M1:M15, and in the formula
bar add
=IF(ISERROR(SMALL(IF(COUNTIF(B$1:B$15,B$1:B$15)1, ROW($A1:$A15),""),ROW($A1:$A15))),"",
INDEX($A$1:$A$15,SMALL(IF(COUNTIF(B$1:B$15,B$1:B$1 5)1,ROW($A1:$A15),""),ROW($A1:$A15))))
and commit this with Ctrl-Shift-Enter, not just enter.
It will show the values in the first column for duplicates in the second.
Copy acroos to the next column to see third column duplicates.
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"BradF" wrote in message
oups.com...
Hi Friends,
I have the following table of pin names as shown below. 1,2,3,4,5 are
the header rows and A,B,C,D... are header columns.
X/Y 1 2 3 4 5
A IO1 IO5 IO9 IO23 IO37 --- Pin names
B IO2 IO6 IO10 IO24 IO38
C IO3 IO7 IO11 IO25 IO39
D IO4 IO8 IO12 IO26 IO40
E IO5 IO9 IO13 IO27 IO41
F IO6 IO10 IO14 IO28 IO42
G IO7 IO11 IO15 IO29 IO43
H IO8 IO12 IO16 IO30 IO44
I IO9 IO13 IO17 IO31 IO45
J VDD VSS IO18 IO32 IO46
K VDD VSS IO19 IO33 IO47
L VDD VSS IO20 IO34 IO48
M IO13 IO17 IO21 IO35 IO49
N IO14 IO18 IO22 IO36 IO50
How do I use INDEX and MATCH to lookup the contents of the table and
display
its corresponding header rows and columns in a separate sheet. I will
use the pinname (cell content) as the lookup item as shown by the
example below:
PIN X-Y
IO1 A,1
IO2 B,1
IO3 C,1
IO4 D,1
. .
. .
IO50 N,5
and also, how do i display the cell headers of cell contents which
appear more the once such VDD in J1,K1 and L1 and VSS in J2, K2 and L2?
How do I make it display VDD = J1,K1,L1
Thanks in advance,
Brad
|