Excel-can it do this??

  fermerboy 23:45 30 Apr 2004

I have to mark a valuation competition which is based like this. There are 4 articles marked A,B,X,Y, . These have to be placed in to the correct desending order and then points allocated as appropriate. The articles are marked prior to being valued so the correct answer can be any order.
What I have is a table with 24 columns and 24 rows(24 possible combinations)and further over a column of 24 possible scores. What I want to do is to have a formula which will look along the top row of the columns(titles)until it finds the one defined as correct by the correct answer cell(absolute reference?) then go down until it finds the same as the competitors answer cell and insert the score to match the answer.
Am I right in thinking that I need to use this lookup function to do this ?
I'm a beginer in the heavy excel stuff.
We have a fancy slide rule thing to do this just now but its really slow and I thought maybe the technology could do it

  VoG II 23:52 30 Apr 2004

Have a look at the download at click here particularly VLOOKUP, MATCH and INDEX.

If you want some more specific help you will need to define your worksheet structure in more detail. For example, where is a correct answer stored and what tells us that it is correct?

  fermerboy 00:33 01 May 2004

Thanks for your input I need time to study that download , I think Hlookup or Vlookup might be what I need.
What I intend to have is on sheet 1 cell A1 the correct answer for the competition, eg : ABXY or YXAB etc. On sheet 2 I have a list of competitors names in column A and the answer they gave in column B, column C will give the result as calculated. Sheet three will have a table 24 columns wide, 24 rows deep ,this gives all possible permutations of correct answer. So if the judge decides that ABXY is correct then I would then want to use the column with that at the top(correct answer) and follow it down till the competitors answer matches and then use the score on that row in the score column to go into the result for them in sheet2 col C.

  VoG II 22:52 02 May 2004

Any luck with this? One thought that struck me - could there be more than one correct answer - if so you need a VBA solution and a totally accurate description of your workbook.

  fermerboy 00:55 04 May 2004

Thanks again for your thoughts.
I have re-read my posts and think they are not very clear.
If I have a table of columns and I want the computer to pick a column to use based on a cell entry elsewhere with a column title in it, the Match function does this and gives a column number,
What I then want to do is look down this chosen column for a cell again based on a cell entry elsewhere to give a row number which will then be carried over to another column to be matched in a similar way in another table and that cell be used as the answer. Vlookup kind of does this but no option to choose the column out of a table. Match would probably do it but I'm not sure how to write the formula. I don't know enough to write one big formula to use and even in steps I'm struggling a bit.
Work has gone it bit busy so apologies for not replying very quickly. I need to get a moment to think hard about it.

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

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Comment savoir si quelqu'un a bloqué votre numéro de téléphone ?