Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last entry in row based on matching value
PHYZX 5.26 5.29 5.31 5.33 5.33 5.33 5.33
NAMAX 11.27 11.34 11.41 11.51 11.57 11.54 11.40 JSVAX 13.45 13.59 13.58 13.68 13.66 13.66 13.42 Assuming PHYZX is at Sheet1!C2, I want to find the last entry for PHYZX from within Sheet2 for use in a formula specific to PHYZX. Value PHYZX can be sorted to any row depending on other criteria, so the formula must always find the last value for PHYZX. I would like to do this for each row. i would prefer not to use VB code. -- newbie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last entry in row based on matching value
see your other post
-- Biff Microsoft Excel MVP "newbie" wrote in message ... PHYZX 5.26 5.29 5.31 5.33 5.33 5.33 5.33 NAMAX 11.27 11.34 11.41 11.51 11.57 11.54 11.40 JSVAX 13.45 13.59 13.58 13.68 13.66 13.66 13.42 Assuming PHYZX is at Sheet1!C2, I want to find the last entry for PHYZX from within Sheet2 for use in a formula specific to PHYZX. Value PHYZX can be sorted to any row depending on other criteria, so the formula must always find the last value for PHYZX. I would like to do this for each row. i would prefer not to use VB code. -- newbie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last entry in row based on matching value
This might be adaptable to your needs. (I do not know where I came upon
this, so apologies to the author) =INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,A1)+1-E2)) When you use this formula you must enter with Ctrl + Shift + Enter. Excel will put curly brackets { } around the formula. And a cell with this formula in it. =COUNTIF(B1:B13,1) So, to test fly this, do this: A1 has the lookup value, I'm using 1 in my sheet. B1:B13 holds various values to be looked up (PHYZX, etc) C1:C13 holds the values to be returned E1 has the COUNTIF formula G1 holds the INDEX formula above (Ctrl + Shift + Enter) Note that in the formula it refers to A1:A13 but on the sheet, only A1 will have a value in it, the lookup value. I believe the reason for this is because the ranges must be of equal size for the formula to work. So put your PHYZX, NAMAX, JSVAX in B1 to B13 Put what you want to return for each of the B entries into C1:C13 E1 will count the number of times a lookup value occurs in column C and return a value which I call an 'instance'. So if I have four 1's in column B then the formula in E1 will return 4 and G1 will return the value adjacent to the fourth instance of 1 in column B. Post back if needed. HTH Regards, Howard "newbie" wrote in message ... PHYZX 5.26 5.29 5.31 5.33 5.33 5.33 5.33 NAMAX 11.27 11.34 11.41 11.51 11.57 11.54 11.40 JSVAX 13.45 13.59 13.58 13.68 13.66 13.66 13.42 Assuming PHYZX is at Sheet1!C2, I want to find the last entry for PHYZX from within Sheet2 for use in a formula specific to PHYZX. Value PHYZX can be sorted to any row depending on other criteria, so the formula must always find the last value for PHYZX. I would like to do this for each row. i would prefer not to use VB code. -- newbie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Auto entry of data based on entry of text in another column or fie | Excel Discussion (Misc queries) | |||
only returning 1st matching entry in table, need to find next matc | Excel Worksheet Functions | |||
matching part of an entry in a cell | Excel Worksheet Functions |