Excel - vlookup function - 2 variables, one result

I'm afraid your question isn't particularly clear. The best way the explain what you want would be to post a sample of the desired result in addition to the original data, sort of "before and after".
There are two pertinent questions to be asked.
1. Are these two spreadsheets in two different workbooks?
The preference would be to have them within a single workbook.
2. Why do you need the formulas?
In the absence of special circumstances it is far easier for the code to write the result than to write the formula. One such special circumstance might be that the workbook from which to draw the answers isn't available at the time of writing the formula. In that case, however, it wouldn't be possible to write a formula unless the name of that workbook would be known.
I see in the Master, row 2, that candidate 1, examiner 730, have a question.
I see in the October sheet that this combination (730 / 1) is found in row 25.
You say to return the theme to Mater!C2. In which column is the theme?
I see that candidate 1 is mentioned in B25 and column AK25. If both results are to be returned (possibly more than 2?) the task becomes quite daunting. I hope that your "before and after" example will shed light on what you need to achieve.

1. Yes, the spreadsheets are from two different sets of data and we are trying, by using these formulae to link them together.

2. We do not need a formulae but I do need a way of collating the data.

Ok, please find the attached 'after' example

=VLOOKUP(B2,'C:\Users\amacfarlane\Desktop\S2 Trial\[NS October 2012.xls]Sheet2'!$A$1:$CI$65536,4,TRUE)

The problem with the formula as shown, is that the column value '4' in this example is manually populated and I want to automate the process.

The themes, whilst being called the same in this example will be different. To try to explain:


Each candidate is seen by 2 examiners at the same time so

Master-Oct-12-with-example.xls

The first thing to do is to combine your two worksheets into one. You can take them apart again  later, but for the purpose of communication two workboooks just won't do. You see, the paths you write will be changed by Excel when I download and open your files on my machine. There is nothing for you to do in this regard right now except to understand that I don't get to see the formulas you write the way you wrote them.

Next, please explain the rules by which data should be transferred.
=VLOOKUP(B2,'C:\Users\amacfarlane\Desktop\S2 Trial\[NS October 2012.xls]Sheet2'!$A$1:$CI$65536,4,TRUE) should return an error because your lookup range has only 3 columns out of which you address the 4th. However, it is clear that you are looking for B2 (the examiner) in column A (list of examiners). So far so good.
Now, you say that you enter the 4 manually. If your first source column is 4 you could replace this number with this formula: INT(COLUMN()/3)+3
Placed in column 3 (column C) it will return 4, placed in column F it will return 5. Each 3 columns you move the formula to the right the number will increment by 1, pointing at the next column.
In my file "October 12" the column  to return is C (=3). In that case you would need to amend the formula to INT(COLUMN()/3)+2.
In the second row of your example your formula points to column 32. The adjusting number would be 31 in that case. You can then copy the formula to the right.
You can avoid tinkering with the formulae altogether if you create a helper column, say column C, and enter the adjustment number there. You could then use a formula like this one: INT(COLUMN()/3)+C2. All you do is to change the number in column C and all your formulae in that row take their cue from there.

Obviously, this method can only be an interim solution. The better way would be to know the rule by which you pick the column. Then a formula could be designed to do the picking fully automatic.

Unfortunately, I shall be travelling tomorrow and all of next week. So, I may not be able to respond to you as quickly as I would wish. I hope you can bear with me.

Hi Faustulus

Thank you for your comments so far.  I have done as you asked and moved the spreadsheets into a single workbook and attach the same here for you.  I have tried the formula you suggested and this is in the file I attach.

I am happy to try to work to an automated solution but understand your travel issues.

Andy
Master-Oct-12.xls

I see that you got the formulas to work with the variable column number but didn't insert a helper column. If this method saves you some time that would be nice.
Meanwhile, in order to proceed please do tell me the rules by which the column is selected.

Hi Faustulus

I have just received some real time data and there are some subtle differences in the layout of the data, so I am going to anonymise it and get it into a semblance of order before I send a new attachment.  Do you want me to close this job and create a new one when ready, or will we just keep this one open?

Andy

I think I haven't been able to assist you substantially until now. Let's continue this thred.
My question is likely to remain the same for the new layout. You need to find a particular student's answers to a particular examiner's questions. The examiner is found by comparing the value in column B of the target sheet with values n Sheet2!A:A. It is essential the column A can have this value only once.
The student's ID isn't equally clear. It seems to be found in various columns of Sheet2 and I would need to understand which columns to consider in the search. Finally, the results you want seem to be in a fixed location relative to the student's ID.

Hi Faustulus

I have had a chance to complete further work on this and attach, hopefully a better example with real data.  I also attach a spreadsheet showing how the examiners meet the candidates.
I have just copied questions asked number for one sheet to the other to represent how the data should be picked up.

Regards
Andy
Master-Test.xls
Examiners-Schedule.xls

Noted.
I'll work n this ver the holidays.
Regards,
Faustulus

The strength of your worksheets’ design is documented in their resistance to reading, printing and data manipulation. Charged with the latter I have found it necessary to transcribe your data to assume a more Excel-friendly format. In the attached workbook the data from sheet Exams are written to sheet Results. The sheet Scores then obtains its data from the Results sheet by means of a formula.
It wouldn’t have been much more difficult to write code that does the entire job, but I suspect that the life expectancy of your Scores sheet doesn’t allow for planning long into the future because it is equally hard to read and impossible to print. If I may advise, I suggest that you capture the data in the format now shown in the Results sheet, then re-design the Scores and Exams sheets to show the extracts you really want to see, for example, the results per student, per examiner, per question or whatever. Applied to the Scores sheet, imagine you enter the Candidate and Examiner numbers at the top of your sheet and get to see the result now displayed in one row, nicely structured for easy viewing and printing along with whatever evaluations you might wish to add. The point I am trying to make is that you never need to see all results of all students and all examiners. Your interest is more likely to be in one at a time.
The code that creates the Results sheet from the Exams sheet is in code module ExamMan. You can simply drag the entire module to your own project in the Project Explorer window of the VB editor while both workbooks are open. Run the code by selecting the TranscribeResults procedure from the Macro list or, from the VBE window, by placing the cursor anywhere within that procedure and pressing F5.
The code will delete any existing Results sheet and create a new one. It will not run if the worksheet Exams isn’t found in the active workbook. You can change both worksheet names in the code
    ' change these names as required:
    Const SourceSheet As String = "Exams"
    Const TargetSheet As String = "Results"
Observe that the name “Results” is also embedded in the formulas in the Scores sheet.
You can change the row and column references specified in the Enums at the top of the code module. I have tried to give very descriptive names to the variables and added some comment. You shouldn’t have too much trouble modifying as required. The code below the Enums and Constants doesn’t need modification when you change the variables’ values.
I draw your attention to cell A1 on the Scores sheet which I have added. The cell displays “Extracted from worksheet ‘Results’” but when you look at the formula bar you will see that its content is merely “Results”. This is an essential part of the system. The difference between cell value and display is achieved by cell formatting. Click Format/Cells to see the format. It is a custom format =”Extracted from worksheet ‘”@”’”. You can modify the text as you wish, but you need the cell’s value to be exactly the same as the sheet’s name.

I suggest to apply the same formatting system in the caption row of your Exams sheet. Format the cells as Custom as ”Session” 0
Then just enter a number in the cell to display “Session 1”, “Session 2” etc. The effect of this change would be that the Results sheet displays just the session number in column B instead of the complete text.
This is the formula in cell C3 of your Scores sheet:-
=INDEX(INDIRECT($A$1&"!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("I")-64)),MATCH(1,(INDIRECT($A$1 & "!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("A")-64))=B3)*(INDIRECT($A$1 & "!C2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("C")-64))=$A3),0),4+INT(COLUMN()/4))
It is an array formula which you must confirm with Shift-Control-Enter in place of the singular Enter required by normal formulas. If entered correctly it will appear between curly braces in the formula bar, if not, it will display an #NA error. It will also display such an error if the lookup values were not found.
Once entered correctly in C3 you can copy and paste it to all other locations where you need it in your Scores sheet. In order to help you understand this formula so that you might modify it if needed I explain its components below in the sequence of their appearance:-
$A$1  -  refers to the name of the Results sheet in cell A1
$A$1&"!A2  -  refers to Results!A2 which is the first cell containing data in the Results sheet
2^16  -  equals 65536 = a number bigger than any number you might have in the Candidate or Examiner columns. It helps find the last row.
CODE("I")-64)  -  returns the number of column I (you can change the letter. Must be capital. Can’t be multiple, like AA)
ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("I")-64))  -  returns something like I500 where 500 is the last used row in column A:A of the sheet specified in $A$1.
4+INT(COLUMN()/4) – the leading 4 indicates the first column. If INT(COLUMN()/4) = 0, 4 points to column D. If your first column isn’t D change the leading number. If you have sets of 6 columns instead of 4 replace the divisor accordingly. The similarity of the two 4’s is purely incidental.
At the bottom of the Scores sheet you will find some snippets of formulas which may help you better understand how the formula works. You will also find the template there on which the formula is based.
130329-Exam-Results.xls

Wow, what a considerable piece of work.  Your explanations are excellent but I just need to get my head round it all before I come back with any comments if that's ok?

Cheers
Andy

Of course, OK. But the longer you wait the more I forget of what I did - Ha,ha. Then I'll need to get my head around it.

Hi Faustulus

I have had a good look at this now and I think I am managing to understand the process.  I know you talk about changing the scores sheet, unfortunately this is the layout that comes from our scanning software and is not changeable.  I run a macro on this sheet to add in the 2 extra columns 'Question asked' & 'Assessor Score', I also hide several columns as they are not used in these 'calculations' (namely Station 1 & 2).

If I could just explain the column titles, relating to Columns :

There are always two examiners marking the questions so the same question numbers are marked by examiner '656' and scores for candidate '1' immediately follow the above scores in Columns Z:AC, AD;AG, AH:AK, AL:AO, AP:AS, AT:AW.

Then the candidate moves to a different station, 'Station 4' in the example and the same process continues for another 48 columns, then to 'Station 5' for another 48 columns.

That is then all the scores for a particular candidate in one row with the scores total at the end.

I tried to copy the formula from Column 'W3' into 'AA3' but I am getting #REF! error.

Can you assist please?

Andy

Hi Andy,
Indeed, there is a logical error in my formula. Here is the corrected version of it:-

[C3] =INDEX(INDIRECT($A$1&"!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("I")-64)),MATCH(1,(INDIRECT($A$1 & "!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("A")-64))=B3)*(INDIRECT($A$1 & "!C2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("C")-64))=$A3),0),4+MOD(INT(COLUMN()/4),6))

Select allOpen in new window

The difference is at the very end where 4+MOD(INT(COLUMN()/4),6) the added MOD function prevents the formula from returning a number greater than 10 (=4+6). The 6 here refers to the 6 result (Themes) columns in the Results sheet.
This method of determining the column number in the Results sheet from the column number in the Scores sheet requires that the latter should always ask for all 6 columns. You can't leave any of them out. Since you have sets of 48 columns I presume that you have no intention of leaving any columns out.
BTW, for your testing this formula may prove useful to you if entered in the Exams sheet in place of scores like 19499.2.

=ADDRESS(ROW(),COLUMN(),4)

Select allOpen in new window

This formula will write the cell reference to the cell which gets copied to the Results sheet and transferred from there to the Scores sheet. You will thus be able to check whether each cell's content correctly ends up at the intended destination.

Thanks again, it is obvious you certainly know your stuff :)

I am away for a few days, but I will try this when I return home.

One further point, if I may?  I will be using this across differing exams and whilst most of them follow the 6 theme format, not all of them do, so is there a particular element of the linking formula that I can tweak to suit the different styles of results files, and, if so, can you point me to it?

Regards
Andy

Hi Faustulus

I have now had a chance to test this out and the work you have done here coupled with the explanations are excellent.  Once I am back at work next week, I will try it on backups of actual live files and I am sure it will be a smooth transition.  Is it OK if I come back to you should I experience any issues?

Thanks again for your efforts, they are very much appreciated.

Andy

Andy,
I'm sure there will be issues. And, of course you are welcome to come back! I shall be glad to help you iron out all the kinks.

Hi Faustulus

Sorry to bother you again but I have been trying all morning to make this work with a proper live file without success.

I am fairly confident that I have made the correct alterations to the formula however, whilst I can pull the data across, it seems that it is not picking the information in the correct order.  It looks like it is picking Theme 4,5,6 first and then 1,2,3 but it should be selecting them in order so that the scores match the question that was asked at that time.

Can you guide please, file attached has been anonymised?
Exam-Anonymised.xlsx

Andy,
Indeed, your testing has uncovered a logical error which I have corrected in this formula:-

=INDEX(INDIRECT($A$1&"!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("I")-64)),MATCH(1,(INDIRECT($A$1 & "!A2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("A")-64))=M3)*(INDIRECT($A$1 & "!C2:" & ADDRESS(MATCH(2^16,INDIRECT($A$1 & "!$A:$A")),CODE("C")-64))=$C3),0),4+MOD(INT((COLUMN()-(CODE("N")-64))/4),6))

Select allOpen in new window

The difference is in the final part,

4+MOD(INT((COLUMN()-(CODE("N")-64))/4),6)

Select allOpen in new window

The COLUMN() function returns the number of the column in which the formula resides which, in your worksheet is column N for the first formula, meaning you want the Theme 1 result in column N. If you move the Theme 1 result to another column please change the letter "N" in this part of the formula to the letter identifying the column where the Theme 1 result should be displayed. Then copy the same formula to all other columns.

Hi Faustulus

I was wondering if I could ask for your guidance? I am having an issue with the formula you provided on the attached spreadsheet.  I have anonymised as necessary.  It is not picking up the correct information.

Andy
PAE-574-Exam-Merged.xlsx

I wrote:
Of course, OK. But the longer you wait the more I forget of what I did - Ha,ha. Then I'll need to get my head around it.

Instead, what I should have pointed out was the function of this little bit of your formula,
CODE("I")-64)
I "I" indicats the last column in your lookup range in the sheet "Results".
Since you increased the number of columns to 8 the last column is now K. The formula, thanks to your modification regarding the number of columns at its end, is actually looking for the correct column but finds that the look-up range doesn't have so many. Hence the #REF error.
Modifying the element in the formula should cure your problem.
CODE("K")-64)

If this job had been done in VBA the problem wouldn't have arisen because VBA permits the use of variables. The number of columns would be one variable and having set that everything else would automatically fall into place. Perhaps you might consider launching another question and get the task transferred to VBA.

Hello again, apologies, I know it has been a long while but our exams work in batches hence the gap.

Just to confirm then my formula should be

=INDEX(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("K")-64)),MATCH(1,(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("A")-64))=AV3)*(INDIRECT($A$1&"!C2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("C")-64))=$C3),0),4+MOD(INT((COLUMN()-(CODE("N")-64))/4),8))

Select allOpen in new window

If this is correct, I am still getting an error when I get to AH3, it should display D21092, then AM, AR, AW should be zero, then BB should be D19993.

More than happy start another question if the transfer would make it easier, as I am trying to streamline processes for my organisation and this is one of the areas I am looking at.

Cheers
Andy

Hi Andy,
Your formula doesn't work for me, either. Fortunately, it appears to be a simple case of shifted columns. The formula you posted arefers to column AV where it should look at M. Please take this "clean" formula and past it to N3. Then make copies only from there.

=INDEX(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("K")-64)),MATCH(1,(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("A")-64))=M3)*(INDIRECT($A$1&"!C2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("C")-64))=$C3),0),4+MOD(INT((COLUMN()-(CODE("N")-64))/4),8))

Select allOpen in new window

BTW, if you really want to go ahead and streamline your data I suggest that you start by designing a sheet on which you have all the data for one student. The requirement should be that the sheet is visible on the screen without scrolling and, therefore, printable in a legible format. You could probably create such a sheet either from your original data or from the 'Results' sheet I programmed for you.
If you have need to comparing several students (or all) you should design sheets with only those columns which you need to compare. Such a sheet (or several, different) could be produced at the click of a button from the same original data.
Post a link to your new question in this thread so that I will be alerted.

I'm afraid I'm still having problems, when I copy and paste the formula then it changes the reference you mention above to the preceding cell each time so in this example AW, the formula looks at cell AV for it's examiner number but then pulls back the incorrect value from the 'results' sheet

In the sheet I have AV3 has a value of 161 and the formula returns the value from Results!D2 which is correct because this is the 9th refereference to 161 out of 8 available. Shouldn't AV hold a value of 737, perhaps? This seems to be the next series' identifyier.

I also failed to mention, apologies, that an additional column has been added in between, there is now 3 columns added before the formula is pasted namely 'Question asked', 'Assessor No' & 'Assessor Score'.  Will this have an affect on the formula?

Also, if I count from M3, there should be 8 instances of 161 before it changes to 737 which there are.

many thanks
Andy

The new columns must affect the functioning of the formula, differently, depending upon where they are. Please post the revised workbook and I'll look into the problem tomorrow afternoon. It's too late for me now.
Look at this as mortal combat. ;-)
Either you will kill the formula or the formula will kill you. Consider the former. Invest your time in advanced worksheet design and you are highly likely to walk away with a vastly improved result presentation in exchange for pretty much the same effort.

Hi Faustulus

I appreciate you looking at this and am concious of your time spent.  I attach a clean sheet for you with the raw data and the extra columns added.

I will start another question post this fix as I really want to automate this and streamline where possible.

Cheers
Andy
PAE-574-Exam-Clean.xlsx

Good morning Andy,

Here is the revised formula,

=INDEX(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("K")-64)),MATCH(1,(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("A")-64))=M3)*(INDIRECT($A$1&"!C2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("C")-64))=$C3),0),4+MOD(INT((COLUMN()-(CODE("N")-64))/5),8))

Select allOpen in new window

The number of columns per set is now 5. Look at the very end of the formula for it. I had explained this ealier.

In order to save you time and effort replace all values on your Results sheet with this formula.

=ADDRESS(ROW(),COLUMN(),4)

Select allOpen in new window

. It makes the job of checking where the data you see in the results sheet come from so much easier.

Excellent, thank you again.

I will get aboard putting my new qiestion together and will post a link here when I have it.

regards
Andy

Here you go, Faustulus

New question

There may be more than one question here, I am more than happy to split it down into further questions if needs be.

Regards
Andy