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