Excel Formula

  SCMODS 20:11 23 Nov 2006
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 2006

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

  VoG II 20:24 23 Nov 2006

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 2006

Knew it, VoG™ to the rescue.

  VoG II 20:27 23 Nov 2006

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 2006

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 2006

But a correction!

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

  John B 20:33 23 Nov 2006

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 2006

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

  SCMODS 22:01 23 Nov 2006

Special thanks to Vog!!!

  John B 03:13 24 Nov 2006

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

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

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Interview: Camille Walala on her giant 3D installation inspired by childhood funfair visits

Best iPad buying guide 2017

Où regarder le British Open 2017 ?