Lookup value

Hi,
I receive a spreadsheet daily which contains much information. I have retrieve one piece of information, a price that relates to a certain code. I need to lookup this reference code in column a and then return the average of the prices in columns b&c and show this value on the top line of the spreadhseet. This row is currently populated with other data so a new line will have to be inserted.

I can manage to achieve using worksheet functions but not VBA.

Please help me.

Thanks
Simon

Originally Posted by BenjieLop

If I understand your post, your formula (array entered) is

=average(if(A1:A1000=E1,B1:C1000))

where:

A1:A1000 are entered the codes;
B1:C1000 are entered the prices;
E1 is where you will enter the code whose corresponding prices (in Columns B and C) you want to be averaged.

Hope this is what you are looking for.

Regards.

No that's not really want I want hopefully the attachment will help. I need to lookup the values associated with LU125 and average them. I need the result to be pasted in A1 which is currently occupied so a row will need to be inserted. This all has to be completed through a macro.

Hopefully this is clear.

Thanks