i need to create a formula for the attached google sheet Testing eye colour
this is what i want it to do with the result being put in D49
1 - I want to get the value in l49 and find the corresponding value in column A 9 - the response to step 8 will be what is put in D49 (in this situation its "22")
2 - using the value in column a that was found, then locate the value in column D that corresponds with the row that found the number from column A in (in this situation its row 24 and "Brown")
3 - i want to then repeat step 2 but use column I instead (in this situation its row 24 and "Blue")
4 - i want to match the response from step 2 to column U (in this situation its either U3, U4, U5 or U6)
5 - i want to match the response from step 3 to column V (in this situation its either V4, V7, V12 OR V16)
6 - i want to locate the row that has matches from step 4 and step 5 (in this situation it will be row 4)
7 - i want to randomly choose one of the cells in that row that are from columns W, X, Y or Z as long as the number is more than 0 (in this situation it will either be W4, X4 or Z4)
8 - when a cell has been chosen, chose the corresponding cell in that column on row 2 from w2:z2
9 - the response to step 8 will be what is put in D49 (in this situation it will either be "Brown", "Blue" or "Hazel"
i have tried to figure it out and use chatgpt but it doesnt work
this is what i want it to do with the result being put in D49
1 - I want to get the value in l49 and find the corresponding value in column A 9 - the response to step 8 will be what is put in D49 (in this situation its "22")
2 - using the value in column a that was found, then locate the value in column D that corresponds with the row that found the number from column A in (in this situation its row 24 and "Brown")
3 - i want to then repeat step 2 but use column I instead (in this situation its row 24 and "Blue")
4 - i want to match the response from step 2 to column U (in this situation its either U3, U4, U5 or U6)
5 - i want to match the response from step 3 to column V (in this situation its either V4, V7, V12 OR V16)
6 - i want to locate the row that has matches from step 4 and step 5 (in this situation it will be row 4)
7 - i want to randomly choose one of the cells in that row that are from columns W, X, Y or Z as long as the number is more than 0 (in this situation it will either be W4, X4 or Z4)
8 - when a cell has been chosen, chose the corresponding cell in that column on row 2 from w2:z2
9 - the response to step 8 will be what is put in D49 (in this situation it will either be "Brown", "Blue" or "Hazel"
i have tried to figure it out and use chatgpt but it doesnt work
