| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Index and Match to lookup headers in row and column?
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup H&V...or match...index??? | Excel Discussion (Misc queries) | |||
| Display index of column headers in column A | Excel Discussion (Misc queries) | |||
| lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
| Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
| Not sure what to use? Lookup / Index / Match etc | Excel Discussion (Misc queries) | |||
Threaded Mode
