Vlookup in combine with an Array Formula

[Mike2]

Hi,

The question is explained, in details, inside the attached workbook.

In general - I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
Thanks.

[Max, Singapore]

One way ..
In I13: =SUMPRODUCT(N(OFFSET(D1,MATCH(D8:D12,C2:C4,0),))*C8:C12)

[Max, Singapore]

Based on your set-up, think you want to do this:

In D14, normal ENTER:
=SUMPRODUCT(N(OFFSET($D$1,MATCH(D8:D12,$C2:$C4,0),))*$C8:$C12)
Copy across to G14

(Same expression as earlier, but primed for copying across)
---
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
---

[Mike2]

"Every day I learn something new and upon learning, I realize how little I know..."

Thanks a lot, Max.

[Max, Singapore]

Welcome, Mike

[Max, Singapore]

"Every day I learn something new and upon learning, I realize how little I know..."

Thanks a lot, Max.

Welcome, Mike
---
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
---

[Mike2]

Max,

I will be more than grateful if you'll explain - in details, as English is not my mother tongue - the reason for "my" Array-Formula not returning the correct value when typed into a single cell - but returns a correct value when typed into 2 cells and more...?

Usually, when I write a formula - I select it [or part of it] in the Formula Bar and press [F9].
[F9], usually displays the result of the formula/part of formula.

Selecting and pressing [F9] on "my" formula displays a #VALUE!error.
So, why does it functioning well when typed into 2, or more, cells !?

Thanks for your time,
mike

[Max, Singapore]

.. the reason for "my" Array-Formula
not returning the correct value when typed into a single cell
-but returns a correct value when typed into 2 cells and more...?

Selecting and pressing [F9] on "my" formula displays a #VALUE!error.
So, why does it functioning well when typed into 2, or more, cells !?

If the above happens, it usually means that the formula is a multi-cell array formula which needs to be array-confirmed simultaneously into a range of cells.

But in your instance here, I'm not sure that your vlookup portion:
VLOOKUP($D$8:$D$12,$C$2:$D$4,2,)
is valid syntax in the first place

[I don't think vlookup can be used like that]

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

[Mike2]

Hi,

I can except the assumption that "my" Vlookup was not properly used - but, the question remains, why such non valid use of function produces a CORRECT value when typed into 2 cells or more...!?

Thanks.

[Max, Singapore]

.. why such non valid use of function produces a CORRECT value when typed into 2 cells or more...!?

I don't know. The "correctness" may be pure coincidence
It may be one of the subtle nuances of Excel

For vlookup, if lookup is text, it's always a good idea to set it to exact match, with the 4th param = 0 (or FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

[Mike2]

Thanks, Max.