Vlookup in a macro for multiple ranges, Help?

**ad9051** · 12-03-2010, 09:09 PM

Ok so im mediocre at using formulae but a slight amature when it comes to macros. I am feeling this needs a macro, although if it can be achieved via a formulae then great.

I have attatched an exaple of the data i am interpreting, which outlines the problem.

Essentially the aim is to input the Top data value of the rank 1 piece of data parrallel to every cell in each data set. In theroy i should be able to do it using some complicated formulae but i keep getting circular refernce.

The reason i would like to be able to do it automatically is i can produce/recieve hundereds of these datasets.

Any advice, or contribution would be greatly appreciated.

Many thanks.

**Paul** · 12-03-2010, 11:05 PM

Hi Ad,

Delete the text from J9 (leave it blank), then put this array formula in J10 and fill it down the column as far as necessary. (After typing or pasting this formula into J10, you must press CTRL+SHIFT+ENTER, not just ENTER.) Also, change the references to $I$500 to a higher row number if your data goes further down than row 500.

=IF(I10="","",IF(J9<>"",J9,INDEX(INDIRECT("I"&ROW()&":I"&ROW()+MATCH(TRUE,I10:$I$500="",0)-2),MATCH(1,INDIRECT("F"&ROW()&":F"&ROW()+MATCH(TRUE,I10:$I$500="",0)),0))))

Hope that helps!

**ad9051** · 12-04-2010, 09:17 AM

Wow that is amazing! Ok im not sure whether this is the norm, but would it be possible to explain how this formuale works, so that in the future i do not need to refer to the forum for help, and might even be able to contribute more to helping others on this forum.

As i said im not sure if this is normal practice or not, but i really need to learn about these array formulae, or even point me in the direction of where is best to learn about them.

Anyways your reputation has jus been boosted many thanks Ad

**ad9051** · 12-04-2010, 02:31 PM

Ok im starting to understand it a little, have manged to successfully use it on my data however i failed to cover one scinario which can occur which leads this equation to not work for all datasets (a huge problem.)

Please see attached spreadsheet it outlines the annomalie not covered by this array formula.

Many thanks

ad

**Paul** · 12-04-2010, 02:56 PM

In the formulas in column I, currently:

=IF(OR(ISNUMBER(B41),(ISNUMBER(C41)),(ISNUMBER(D41))),(LARGE(B41:D41,1)),(""))

Can it be changed to:

=IF(OR(ISNUMBER(B41),(ISNUMBER(C41)),(ISNUMBER(D41))),(LARGE(B41:D41,1)),0)

This way a 0 will appear in column I instead of a blank, leaving blanks only where there is truly no data in previous columns (the blanks in between data sets). Unless you have data sets containing mostly negative numbers, 0 would never be returned as the #1 rank (just an assumption).

If that can't be done, we can probably tweak the array formula I gave you to include another logic check.