Checking content of cells to the left and right of a cell that contains a TRUE string for a search

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a list of 10 names in column D1:D10.

I have three columns of data that I need to sort to get what I need from.

A1:A130 contains the number of tries between 1 and 6 that a student has attempted
B1:B130 contains the list of students names in attempt order. The names will be one of the listed names in D1:D10 but in attempt order
C1:C130 contains the results which are all between 0 - 1000

In columns E1 (student name) I want the name of the student starting with D1.
In column F1 (1st attempt) I want to search column B1:B130 for the student I have placed into E1 and if I get a match, I need to then to check the cell immediately to the left of the matched cell (this is the bit I am having difficulty with) and if that is set to "1", then I want to place the results from the cell immediately to the right of the name matched cell into F1 as the result.

Columns G1 - K1 will be the same but checking for a "2", "3", "4", "5", and finally "6" which correspond to the 2nd through 6th attempt. Some may only have 3 attempts.

I am struggling to find the right formula that will work.

My end result I want to look like this

E1 (student name), F1 (1st attempt score), G1 (2nd attempt score), H1 (3rd attempt score), I1 (4th attempt score), J1 (5th attempt score), K1 (6th attempt score)

then continue down until I have all the student information

E2 (student name), F2 (1st attempt score), G2 (2nd attempt score), H2 (3rd attempt score), I2 (4th attempt score), J2 (5th attempt score), K2 (6th attempt score)
E3 (student name), F3 (1st attempt score), G3 (2nd attempt score), H3 (3rd attempt score), I3 (4th attempt score), J3 (5th attempt score), K3 (6th attempt score)

.... etc until all 10 students and their corresponding results from each attempt has been recorded.

I hope this makes sense, but if not please let me know what else would be useful.
Paul
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top