Vlookup to look up an array of values

[kev.wong1991]

Hi all,

Is it possible to use VLOOKUP on an array of values, and then average those values returned? For example, I've tried {Average(Vlookup({A1:A3},F1:G500,2,False))} and it only VLOOKUPs the value in A1. Is it possible to look up also A2, and A3 in that formula without using VBA?

Thanks!

[Miraun]

Hi Kev.Wong1991

Without an example, I can't go too much deeper, but you may want to try using an AVERAGEIF function. Or if you're looking for array A1:A3 to all be true, you can use the AVERAGEIFS function to hopefully reach the same goal.

Give it a shot, and let us know if this does what you're looking for!

[sktneer]

You may try this Array Formula to see if you get the desired result. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter.

Please Login or Register  to view this content.

[kev.wong1991]

Hi all,

Thanks for taking the time to answer this, I think sktneer's solution is on the right track; however let me give a bit of background on the problem so I can take the solution one step further.

Basically, in column B, I have a list of 750 values which I want to be ranked largest to smallest. In column A, correspondingly will be numbers that identify the number in column B. For example, in column B, I'll have a number 1000. In Column A, that number is represented as 32.

Column F contains the same set of numbers that are in column A, so it will also have 32 in it. Correspondingly in column G, it'll have a different number, say 80.

Eventually, I'd like to have a formula that can:

1. Find the largest 100 numbers in column B.
2. Find the corresponding identifier number in column A.
3. Find the corresponding identifier number in column F.
4. Find the number associated with that identifer number in column G.
5. Average the largest 100 numbers in column G, based on the rankings found in column B.

The formula provided by skeetner averages the first 100 numbers (A1:100), but ideally I'd like to find a way to average this without having to manually sort by using a Large function.

Thanks a lot!

[Tony Valko]

Is something like this what you want to do?

Data Range

	A	B	C
1	90		71.16667
2	74
3	70
4	69
5	62
6	62
7	60
8	55
9	27
10	9
11	------	------	------

Get the average of the largest 5 numbers (and ties):

=AVERAGEIF(A1:A10,">="&LARGE(A1:A10,5))

I have the data sorted just to make it easy to see what numbers should be averaged.

If you want the largest 100 numbers averaged, in the formula adjust for the correct range and change the 5 to 100.

[kev.wong1991]

Umm, no not exactly.

So say I have data like this in the below:
A B C D
12 100 30 20
16 125 15 28
15 135 16 23
10 99 12 12
09 150 10 23

So basically, I'd like to Rank B in terms of largest to smallest. Then, it should look like this:

A B C D
09 150 30 20
15 135 15 28
16 125 16 23
12 100 12 12
10 99 10 23

Then, I'd like to find the say, top 3 from column A in column C and then average the corresponding number in column D.
Following the above example, it would be looking for number 09, 15, and 16 in column C and find the corresponding number in column D: so, 23, 23, 28 respectively. It would then average 23, 23, and 28 to result in: 24.6667

Hope this clears things up a little better!

[Tony Valko]

Sorry, the explanation doesn't seem to match the sample data.

looking for number 09, 15, and 16 in column C

There is no 9 in column C. Did you mean column A?

the corresponding number in column D: so, 23, 23, 28

Did you mean 20, 28 and 23?

[kev.wong1991]

Yes sorry that 30 in Column C is supposed to be a 9. so, an average of 20, 28, and 23.

[Tony Valko]

Ok, if you sort column B in descending order then it seems to me that columns A and C are irrelevant. Right?

Data Range

	A	B	C	D	E	F
1	9	150	30	20		23.66667
2	15	135	15	28
3	16	125	16	23
4	12	100	12	12
5	10	99	10	23
6	------	------	------	------	------	------

Formula entered in F1:

=AVERAGEIF(B1:B5,">="&LARGE(B1:B5,3),D1:D5)

[kev.wong1991]

Yes, but unfortunately column A and B are located on one sheet, and column B and C are located on another sheet. They aren't going to be so easily compared/sorted so column A and C are the identifiers to see which numbers to average in column D. It can be done manually, but ideally it's done automatically since this formula will be pasted in a template which gets reproduced through a macro that populates the data.

[Tony Valko]

Well, at this point I'm totally confused!

Maybe someone else will understand it.

[kev.wong1991]

I think sktneer was on the right track.. that formula worked out if column B and A were sorted in the right order.

I think right now I'm trying to find a Large() formula to find the largest 100 values in column B, find the address, find an offset (,-1,,) to get the number to VLOOKUP and then match that in Column C to find the Column D corresponding data.

Is that a possible method?

[Tony Valko]

If you can post a SMALL sample file so we can see your data in context, it may help us better understand what you want to do.

20 rows worth of data is plenty and tell/show us what results you expect.

[kev.wong1991]

AverageExample.xlsx

Here! Let me know if I can clarify any further. Thanks for all your help so far!

[Tony Valko]

OK, this array formula** returns the expected result when referencing the sorted table.

=AVERAGE(IF(ISNUMBER(MATCH(C26:C45,A26:A35,0)),D26:D45))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.