PDA

View Full Version : Get the position of the data in the table



jignesh142
01-28-2014, 10:26 PM
I have table like this 1 2 6 5 2 5 2 4 3 6 7 9 2 2 4 1 now i want to get the position of each data in the table, how can i do so? I have tried with the rank formula but cannot be used for the duplicate entries.

mancubus
01-28-2014, 11:15 PM
is it a one row table with repeating values? and if you are wanting to learn the position of, say, 2, which 2 is it? there are 5.

jignesh142
01-29-2014, 12:29 AM
Hi, mancubus, i don't know how to format the table in this forum but it is like first row contains 1 2 6 5, second row 2 5 2 4, third row 3 6 7 9, fourth row 2 2 4 1 i want the position of every 2 that are in the table

mancubus
01-29-2014, 01:06 AM
hi. you can use worksheet function MATCH to return the relative position of a value. ----- assuming your table is in columns A-D, with no header row: in E1 : =MATCH(2,A1: D1,0) copy down. args: lookup_value, lookup_array, match_type. 0 in the formula is for exact match. to learn more visit: http://www.contextures.com/xlFunctions03.html