Looking up values

[mr_teacher]

Hi,

I have a spreadsheet which has not been set up well for the needs I have but need to extract data from this. I have uploaded a sample of the data.

I need to look at Sheet 1, and find a child's name. This then needs to look across and find where a 1 has been entered (this is wherever the cell is coloured red - it has been hidden by conditional formatting). This will only ever happen in one place. This then needs to look at the column header (highlighted in yellow) and record only the final two letters - so it does not put in the word 'Level' I have put an example of what the result will look like in the sheet 2 tab. This will need to be able to do the same for the W10, W20 and W30 data.

I hope that this makes sense.

Thanks for any help you can offer.

[ginganababy]

if you copy the line 4 (data with the diferent levels) to the line 43 for example... you can the use this Hlookup formula in a column beside the names

=RIGHT(HLOOKUP(1;F8:EM1008;COUNTA(D8:$D$41)+2;0);3)

it worked for me

try it out

[mr_teacher]

I might be missing something - but where do you mean to enter this formula? Is this on sheet 1 or sheet 2?

I need it to look and only find if the '1' is entered under any of the 'B' columns at first and return the level from the header in Sheet 1 in column B on Sheet 2. Then it needs to look through and see where the '1' is entered for the W10 column and so on... It needs to do this for each child.

Thanks

[teylyn]

Hi mr_teacher,

you need to lose the merged cells in row 4. They will hinder any lookup formula, at least, they won't be able to be processed as a lookup result.

If you enter the two digit code into row 3, then you can use this formula in B4 on Sheet2, copy down and across.

Please Login or Register  to view this content.

see attached. I've only entered a few values for row 3, you need to do the rest yourself.

cheers

[sailepaty]

I think an auxiliary column will simplify the formula and speed the calculation.

Insert a column in Sheet 2 before the names, column A. Use this formula and copy it down.

=MATCH(1,INDEX(Sheet1!$E$7:$EL$41,MATCH(B4,Sheet1!$D$7:$D$41,0),),0)

Use this formula in cell C4 and copy it down and across.

=IF(ISNA($A4),"",RIGHT(IF(INDEX(Sheet1!$E$6:$EL$6,$A4)=C$1,INDEX(Sheet1!$E$4:$EL$4,1+6*INT(($A4-1)/6)),""),2))

Regards