Excel Equation Not Working?!

Copper Contributor

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

@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.

@RVLIES905 

=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.

This is great! How could I add an additional match?
I need to select 1 of 4 age groups before listing the name.

@RVLIES905 

=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.

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

@RVLIES905 

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.