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

=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

Hi Bob,
This is great and it works. It displays the pin name at the
intersection of the X and Y
however I want to do the other way around, given a pin name, I want to
know its corresponding X and Y headers such that given IO1, excel
function will give me A and 1,
given IO2, it will give me B1 and so on. Thanks for the help.

Brad

Bob Phillips wrote:
=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

On Dec 13, 11:11 am, "BradF" wrote:
Hi Bob,
This is great and it works. It displays thepinname at the
intersection of the X and Y
however I want to do the other way around, given apinname, I want to
know its corresponding X and Y headers such that given IO1, excel
function will give me A and 1,
given IO2, it will give me B1 and so on. Thanks for the help.

Brad

Bob Phillips wrote:
=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$ 15)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
roups.com...
Hi Friends,

I have the following table ofpinnamesas 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 ---Pinnames
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 useINDEXandMATCHto 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