Dec 30 2021 05:52 AM
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!
Dec 30 2021 06:38 AM
@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.
Dec 30 2021 07:24 AM
=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.
Jan 10 2022 10:03 AM
Jan 10 2022 10:47 AM
=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.
Jan 11 2022 07:02 AM
Jan 11 2022 07:09 AM
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.