Using IF & Lookup Function

Hi,

I had some help last week to solve this function which I was most appreciated with, however, I've realised that I need a bit more detail.

Basically I was trying to get an outcome/level to correspond to a score in a test so I could triangulate data sets.

With my new example, I've added in an O3 for the criteria box also, which means D18 doesn't show correctly.

I also can't get the formula to work for the bottom, even with the old criteria.

I have attached the spreadsheet along with the answers I would expect.
O2c, O2b, O2a = O2 (Outcome_ etc. whilst 5a, 5b, 5c, = L5 (Level) and so on

Any help appreciated as always
Thanks

Hi Ali,

Thanks for the reply, that makes sense. I have now redefined the criteria. Could you possibly help now?

Many thanks

Hi,
Disregarding my previous request, I would like the If Lookup formula to work on the bottom section of my excel sheet also please.
Thank you

Hi Ali,

I'm glad you sympathise with a fellow teacher!

Much like in the top half of my table I've managed to get the 'Match' column to see whether the test score matches the outcome, I want the same to happen for the Level & test scores in the bottom section.

On the top table it has recognised O5 as O5c, O5b, and O5a. I want the same for the bottom table, therefore it to recognise a 'level' 5 as 5a, 5b, or 5c, using the lookup table.

The second 'match' column looks up the new results, therefore 'triangulating' the data. However a Y in the first match section would supersede any result in the second set of test results.
I hope that makes sense?

I have reattached the excel sheet which should hopefully make it more clear in regards to the criteria table.

Thanks once again

Hi,

Thanks for the reply. The VLookup for column D worked perfectly, brilliant!

Could you please advise on column G. For this to work it needs to look at column E & F (it may help looking at the top half and how this worked?)
I can't seem to get my head around it unfortunately.

Thanks

Brilliant, last piece of the puzzle solved.
Much appreciated Ali

Hi Ali,

Sorry to be a pain one last time but I decided to add an extra column in a different part of my sheet for an extra test. Could you please help me with a new formula for Column H, as it doesn't appear to work now.

I appreciate all your help so far
Many thanks

Hi,
Thanks for the reply.
I believe H5 should be Y as they scored 83, which is in the criteria range of 69-84 for O4, likewise H26 should be Y as a score of 114 is in the Level 5 criteria range (110-124). I hope that makes sense?

I appreciate any further help

Hi,
I want the second 'match' column (H) to look up scores from column E, F, G. If ANY (1 or more) of these scores correspond correctly to the criteria given in the tables I would like it to show Y in column H. If none of these do then it would show N.

Apologies if this was not clear in advance, and thank once more for taking your time to reply

Hi,

I want the first 'match' column (P.W.AE) to look up scores from the column to the left, and see if it corresponds to the correct outcome/level against the criteria in the table AK/AL. I also want the second 'match' column (S, AA, AI) to look up scores from three columns the column to the left, against the outcome/level against the criteria in the table AK/AL.

If ANY (1 or more) of these scores correspond correctly to the criteria given in the tables I would like it to show Y in column H. If none of these do then it would show N. If Y is already in the first match column, it would show Y in the second match column irrespective if those do not correspond.

I posted this previously and appreciated the help, but have since added more columns which I could not get the formula to work. Pupil 2 in FPh sheet should show as Y in second column, as should (KS2) Pupil 7 in English, Pupil 4 in Maths/Procedural as well as a few others (clearly the current formula needs amending).

Apologies if this was not clear in advance, and thank once more for taking your time to reply

Hi,

I want the first 'match' column on each table & page (P/W/AE), to look up scores from the column to the left (O/V/AD), and see if it corresponds to the correct outcome/level (N/U/AC), against the criteria in the table (AK/AL). I also want the second 'match' column (S, AA, AI) to look up scores from three columns the column to the left (Q-R, X-Z, AF-AH), against the outcome/level against the criteria in the table AK/AL.

If ANY (1 or more) of these scores correspond correctly to the criteria given in the tables I would like it to show Y in column H. If none of these do then it would show N. If Y is already in the first match column, it would show Y in the second match column irrespective if those in the second set of scores do not correspond.

Thank you for taking your time to reply

Fantastic, the comparative FPh works accurately.

Would you happen to help on the KS2 one also please? I have tried copying and pasting the formula given for FPh but it doesn't seem that straight forward.
Many thanks

Now I see, much appreciated again.
Thank you

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.