I have 2 columns of data, 1 of which is repeating data, and im looking for a formula to give me the ROW # for which the values in 2 cells are TRUE.
Input Cell A1: code001
Input Cell A2: 30BE
Value that i need: the ROW # that contains both of those values. (in this example, i would need it to return ROW 2)
column A column B
code001 15BE
code001 30BE
code001 45BE
code001 60BE
code002 15BE
code002 30BE
code002 45BE
code002 60BE
etc. etc.
Can anyone help? Im thinking its a combination of MATCH, INDEX, VLOOKUP, and/or HLOOKUP??
i'd do it this way putting a helper column in c
then B1 will give row.(but remember if two lots of data are repeated it will only give first row found)
Last edited by martindwilson; 06-18-2008 at 06:22 PM.
Thanks, thats kinda what i did (without the space), just didnt know if there was a way around it.
ok try using either of these array formulas
they need to be entered with ctrl+shift+enter
this applies to sheet I have attached it's in C2=MATCH(A2 & CHAR(1) & B2, A4:A11 & CHAR(1) & B4:B11, 0)+3
or
in D2=MATCH(1, (A2=A4:A11) * (B2=B4:B11), 0)+3
apparently the second is the one to go with! no idea why
you would have to adjust cell ref as required
note the +3 at end is to return the correct row from sheet as data does not start until row 4 else it would tell you say, martin smith was in row 1
Last edited by martindwilson; 06-21-2008 at 09:30 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks