If countif equals then lookup?!?

Hello everyone,

First of all, this is the first time I have posted here because it is the first time I have not been able to find the answer else where on the forums. Kudos to everyone who helps out on here. We are the silent majority who thank you.

What I am trying to do is this;

If a range of cells contains a specific number then lookup some data. This is the formula that I am using.

=IF((COUNTIF(G7:G53;1)=1);LOOKUP(1;G7:G53;D7:D53);"")

So if the number one is contained just once anywhere from G7 to G53 then it will look up the corresponding data from D7 to D53. I have this set up four times so that the corresponding data in column D is supplied when the number one appears once, number two appears once; number three appears once or the number four appears once. If the numbers that are entered into column G are in the order 1234 then it works fine. If the numbers are entered in a different order, (4321 for example) it doesn't.

Can anyone explain where I am going wrong?

Thanks

L

Albert 4
Bobby 2
Charles 3
David
Eddie
Frank 1
Gordon

What I would like is that in a separate part of the same table, the data, is returned:

Frank
Bobby
Charles
Albert

When the numbers are in order, it works. When they aren't, it doesn't

L

Book1.xls

Here's a sample

If you swap the numbers for 1234, it works.

I don't get it...

Originally Posted by TMShucks

That's not a workbook, that's a list. I need to see where the data is and where your formulae are.

Regards, TMS

I have now done so. I couldn't work out how to do it at first.

It works, it works!!!!

Thank-you very much. Have a biscuit.

L