Excel: Results in different cells??

  MAJ 20:38 19 Jul 06
Locked

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 06

".....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 06

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 06

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 06

:)

  ArrGee 12:30 20 Jul 06

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

  VoG II 13:23 20 Jul 06

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 06

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 06

bookmark

  MAJ 21:10 20 Jul 06

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.

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