• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP to turn one array into another?

Hey Montrey,


Just on my iPad after a long dinner party so will have to check it out in the morning. But your excitement is clear!


Unless the pasted formula says it all, can you forward your .xls to the gmail address we created as well?


More feedback tomorrow.


Doug
 
Nice Formula Montrey


It is a Pseudo Array formula as Sumproduct takes its input ranges and treats them as Arrays without having to CSE the formula.
 
Nevermind about sending, I can get to the above link. The use of LOOKUP in that way is a bit mystifying - I can't find that technique documented anywhere. How did you find it? Again will give it a whirl when on a real computer and can break it down. Looks promising.
 
No worries. I just looked at it. It appears to work. And it serves my three primary use cases:


1) I can add a new consultant and fee into the fee table, as required

2) I can add any consultant to any project and the summaries automatically update

3) I can create a new project mapping and add it to the summaries table


I was still mystified as to why this worked because there is no documentation on this behavior (if you have a link you should it to this conversation). But breaking this down it looks like

[pre]
Code: 
=INDEX($J$2:$J$5,,1)

creates a lookup vector of names, whereas


=INDEX($K$2:$K$5,,1)
[/pre]
creates the result vector of fees. (It seems like this happens in SUMPRODUCT implicitly as Hui points out, but by themselves, you have to create these as array formulas to see the results).


Anyway that's the mapping specification. Then LOOKUP with a lookup value *that is a range* uses the lookup->result vectors to map each member of the lookup value array (which are names) to a fee. It even works if the lookup value array is of different length to the lookup/result vectors. Cool!


Ergo - exactly what I was looking for out of VLOOKUP if you passed a lookup "value" that was a vector.


I agree, you found it. Brilliant!
 
Ack. One more point I discovered after some debugging: the lookup and result vectors had better be sorted by the lookup vector as the sort key (INDEX($J$2:$J$5,,1) in the above example). Maybe this isn't news to anyone, but to me, it was a try of desperation as I watched the LOOKUP function return seemingly arbitrary selections from the result vector.


As soon as I sorted both vectors with the lookup vector as the key, the LOOKUP worked as expected.


It's a bit of minor maintenance for new additions to the fee table, and I looked for ways to sort arrays inline in the formula, but they are all non-trivial, obfuscating. For now I'm hand sorting the fee table and walking away.


FYI. Mileage may vary.
 
You must log in or register to reply here.
Back
Top