 Excel Equation Not Working?!

I need an equation (that works) to show the value of a cell in a different sheet.

for example: Sheet1 has Column A as a person's name. Column B, C & D have numbers 1-60 listed. (So each person could have up to 3 numbers, one in each column. Each number only listed once).

Sheet2 has the numbers 1-60 listed. I need to show the name of the person that corresponds with the number from Sheet1.

Here are my attempts.

=INDEX(Sheet1!B15:D38,MATCH(21,Sheet1!A5:A38,FALSE))

=IFNA(INDEX(Sheet1!A6:D29,MATCH(A3,Sheet1!B6:B29,FALSE),1),INDEX(Sheet1!A6:D29,MATCH(A3,Sheet1!B6:B29,FALSE),1))

=IFNA(INDEX(Sheet1!B5:D29,MATCH(A3,Sheet1!B5:D29,FALSE),1),INDEX(Sheet1!B6:D29,MATCH(A3,Sheet1!B6:D29,FALSE),1))

=VLOOKUP(A14,Sheet1!A5:D28,Sheet1!A:A,0)

HELP!

6 Replies

Re: Excel Equation Not Working?!

@RVLIES905 Please see attached workbook containing three possible solutions.

In Sheet2, column B contains a formula that works in all Excel versions. Column D works only in MS365 and 2021. Column G contains PowerQuery output. The latter can be viewed, edited and refreshed in Excel for the PC (later than 2013). It can be refreshed only in Excel for the Mac. See which one works best for you.

Re: Excel Equation Not Working?!

=INDEX(\$A\$1:\$A\$10,MATCH(1,MMULT(--(\$B\$1:\$D\$10=F1),{1;1;1}),0))

An alternative would be above formula. If you don't work with Office365 or 2021 enter formula as arrayformula with ctrl+shift+enter.

Re: Excel Equation Not Working?!

I need to select 1 of 4 age groups before listing the name.

Re: Excel Equation Not Working?!

=INDEX(\$A\$1:\$A\$10,MATCH(1,MMULT(--(\$B\$1:\$D\$10=H1)*(\$E\$1:\$E\$10=G1),{1;1;1}),0))

Maybe as shown in the attached file.

Re: Excel Equation Not Working?!

I assumed that would work as well, but I am getting an NA....??

Re: Excel Equation Not Working?!

What Excel version do you work with? If you don't work with Office365 or 2021 you have to enter the formula as arrayformula with ctrl+shift+enter. Maybe you want to attach a file that shows your data, formula and error message.