Help with macro in excel

Closed
excelwip Posts 1 Registration date Sunday December 28, 2014 Status Member Last seen December 28, 2014 - Dec 28, 2014 at 11:29 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 29, 2014 at 04:43 AM
Hi

I need some help in writing a macro for the below:

1. In sheet 1, I have column A with a list of items and many of them will be repetitive, Column B with the current cost for the item. we have column C and D that need to be filled with values from sheet 2
2. Sheet 2. Column A contains the same list of items from column A in sheet 1 but this time it is unique. It is not repetitive. Each items have the cost per month. So column B (header: Jan 2015) has values for month of Jan, Column C (header name Feb 2015) has values for month of Feb and so on.
3. Now I need to look up the value from Cell A2 in sheet 1 with the range column A in sheet 2. Once we look up the value in cell A2 use that row. From that row find the value that is greater that value on Cell B2 in sheet 1 and appears first. For example if value of B2 is 5, we need to find the value greater than 5 in the row we found using first lookup.
4. Once we found the value greater than cell B2 in sheet 1, we need to get that value in Column C in sheet 1
5. The last step is to get the column header of the first greatest value and bring it in column D in sheet 1

I can attach an example sheet if needed

Please help me with a macro to get the values

Thanks

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 29, 2014 at 04:43 AM
not clear
suppose data is like this

name data
a 1
s 2
d 3
d 4
s 5
a 6
a 7
s 8
d 9


suppose d2 has entry "s" (without quots)
in E2 have this formula

=MAX(IF($A$2:$A$10=D2,$B$2:$B$10))
invoke this formula by
CONTROL SHIFT ENTER

is this what you want????????

you can change D2 as "a" or "d"
0