How to use Index and Match to lookup headers in row and column?

  • Thread starter Thread starter BradF
  • Start date Start date
B

BradF

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$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)
 
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
 
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
Click to expand...
 
Back
Top