Excel: Results in different cells??

  MAJ 20:38 19 Jul 2006

How do I explain this correctly (scrathches head)?

I have a list of computers and their specs. The columns are named:

A1:"Computer Name", B1:"Computer Code", C1:"Hard Drive Size", D1:"Amount of Memory".

There are ten computers in this list, that's 4 Columns (named above) and 10 Rows which contain the relevant information for each computer (total 4 columns and 11 rows: A1:D11).

What I want to know is:
Is there a way that I can enter a particular computer's code in [say] cell A15, so that when that code is entered in A15, it's "Computer Name" is returned in B15 and it's "Hard Drive Size" appears in C15 and it's "Amount of Memory" appears in D16, all at the same time?

Hope I've made that understandable.

  MAJ 20:48 19 Jul 2006

".....and it's "Amount of Memory" appears in D16, all at the same time?"

That should read:

......and it's "Amount of Memory" appears in D15, all at the same time?

  VoG II 20:57 19 Jul 2006

You need a combination of INDEX and MATCH for the first one, MAJ, and then a VLOOKUP for the others. Unfortunately Crimewatch is on in a minute or two and I need to make sure that I'm not featured! I'll return to this later. Anyway, be assured that it can be done.

  MAJ 21:05 19 Jul 2006

I would watch for you on CW, VoG™, but herself is drooling over Gordon Ramsey on th'other side, thank God for the sanctuary of the computer room. :)

I look forward to seeing how it's done, VoG™.

  MAJ 11:38 20 Jul 2006


  ArrGee 12:30 20 Jul 2006

Cheers for that VoG. Been trying to find a way to do this myself.

  VoG II 13:23 20 Jul 2006

In B15 =INDEX(A2:A11,MATCH(A15,B2:B11,0),0)

In C15 =VLOOKUP(A15,B2:C11,2,FALSE)

In D15 =VLOOKUP(A15,B2:D11,3,FALSE)

Incidentally if you transposed columns A and B then you could use VLOOKUP for the Name as well.

Sorry for the delay - work intervened.

  MAJ 16:45 20 Jul 2006

As usual, that's top notch, VoG™, works a treat, many thanks for opening my eyes again. I see what you say about transposing columns A and B. I might have a play around with that see if I can manage to do it, can't have you doing everything. ;)

  Monoux 17:47 20 Jul 2006


  MAJ 21:10 20 Jul 2006

Got it sussed, VoG™, it was dead easy after studying your formulae. Many thanks again, Exccel Guru. :)

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

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?