# Excel Formula

SCMODS 20:11 23 Nov 06
Locked

Hi

I am struggling to find the right formula for the following:
I have a short list of ID numbers in Column A which I am using to choose the same ID numbers in Column B (this is the full list of ID numbers). From Column D onwards there are regular fields like organisation name, Address 1, Address 2 etc which are matched with column B. Column C is where I would want the result of the formula to go. Basically for each ID number in B the formula needs to look in A and if the number is the list / range indicate this in column c against the relevant number (eg true false yes no etc). Is this possible?

Cheers

Totally-braindead 20:13 23 Nov 06

Vog will answer if hes online at the moment, hes an Excel expert.

VoG II 20:24 23 Nov 06

Is there any chance that you can transpose columns A and B (so that the value in Column A is the one to be looked up)? If so this is simple - assuming that you are starting in C2, then in C2

=VLOOKUP(A2, B2:B100, 2, FALSE)

(change the 100 to suit).

Then click in C2, hover the cursor over the bottom right of C2 until the cursor displays as a + and double-click to copy the formula down.

If you cannot transpose the columns then you will need a combination of INDEX and MATCH - please post back if that is the case.

Totally-braindead 20:25 23 Nov 06

Knew it, VoGâ„˘ to the rescue.

VoG II 20:27 23 Nov 06

Actually, having re-read the question, the formula would be:

=IF(ISNA(=VLOOKUP(A2, B2:B100, 2, FALSE)), FALSE, TRUE)

Legolas 20:29 23 Nov 06

I am always very impressed with your Excel acumen not only in knowing the formulae but in grasping what is needed.

When I look at it is like double dutch to me. I have done a couple of very simple formulae in Excel but mostly it just gives me a headache trying to work it out. Your title of Excel expert is well earned.

VoG II 20:33 23 Nov 06

But a correction!

=IF(ISNA(VLOOKUP(A2, A2:B100, 2, FALSE)), FALSE, TRUE)

John B 20:33 23 Nov 06

The website 'You tube' tells me JavaScript may be turned off.

Is there a way to check this please, and how do I turn JavaScript on (if it's off?).

Thanks

John

SCMODS 21:58 23 Nov 06

Many thanks to all who replied. I'll try in the Morning.
Cheers

SCMODS 22:01 23 Nov 06

Special thanks to Vog!!!

John B 03:13 24 Nov 06

I wondered where my post had gone! Very sorry SCMODS.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now