Lookup Formula

[adam2308]

Hi,

I am looking for a lookup formula to transfer a list from one order to another list in a different order.

What I want to do be able to do on the attached example is enter a letter (A-J) in column C and then column H to self populate. So as per the attached example; I want H2 to pickup Switzerland from column B (as the first country in group A), H3 to pickup Germany from column B (as it is the second country in the list to be in group A), H7 = Portugal (first country to be in group B), and so on, and so on...

Ideally I want to keep to using a formula for this rather than adding a process for the user to either re-sort the data or to use any VBA. I've tried to find a suitable lookup formula or a combination of lookup formula's but I just can;t seem to work it out!

Any help would be much appreciated.

Thanks,
Adam.

[63falcondude]

Try this in H2:

=INDEX(B:B,SMALL(IF(C$2:C$56=F2,ROW(C$2:C$56)),G2)) Ctrl Shift Enter

[Glenn Kennedy]

An array formula in H2, copied down:

=INDEX(B:B,SMALL(IF($C$2:$C$56=F2,ROW($C$2:$C$56)),G2))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

or...

a non-array (just use ENTER) alternative:

=INDEX($B:$B,SMALL(INDEX(($C$2:$C$56<>F2)*10^10+ROW($C$2:$C$56),0),G2))

[xjohnson]

Please remove this is wrong

[xjohnson]

Never mind most likely Glenn has a better solution.

Sorry,
XJ

[AlKey]

another non-array formula
Enter in H2 and copy down

Please Login or Register  to view this content.

[Glenn Kennedy]

OP's profile says 2007, so no AGGREGATE function.

[AlKey]

OP's profile says 2007, so no AGGREGATE function.

I guess I was looking at the wrong OP
Then this one

regular formula

Please Login or Register  to view this content.

[Glenn Kennedy]

Alkey... YOU making a mistake.... there must be a blue moon in the sky tonight!! Your latest looks like a minor variant of mine...

[AlKey]

Alkey... YOU making a mistake.... there must be a blue moon in the sky tonight!! Your latest looks like a minor variant of mine...

I introduced NOT function to make formula more logical (<>F2 to =F2).

[adam2308]

They work perfect - thanks guys.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.