Log In
Register
Home
Power BI
May 13, 2021
Offline
Attached is a workbook that I used to code a TB. Sheet 2 is my reference sheet and 3 is the one where I paste the accounts I need to code. For some reason this time it's not working. Any ideas? Attached is the workbook.
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Perhaps like in the attached workbook?
Power BI
May 13, 2021
Offline
Yes - thank you! can you explain what I had done wrong?
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Well, when you look at my formula you can workout the correct syntax.
=INDEX(Sheet2!$C$2:$C$360,MATCH(Sheet3!A3,Sheet2!$B$2:$B$360,0)) or in words
=INDEX(list with account numbers,MATCH(account name, list of account names,0))
Your original formula would have worked like this:
=IFERROR((INDEX(Sheet2!$B$2:$C$501,MATCH(Sheet3!$A3,Sheet2!$B$2:$B$319,0),2)),"")
returning the value from the second column in a range with account names and numbers, where the name in sheet 3 matches with the number (column C) in sheet 2. Obviously that doesn't work. Furthermore, you formula (see below) indexed a range from row 3 and down, thus skipping the first row. Not your intention, I believe. Furthermore, I'd also recommend to use consistent index and match ranges. Your formula will only work if the match is found up to row 319 where the list of indexed range spans through row 501.
=IFERROR((INDEX(Sheet2!$B$3:$C$501,MATCH(Sheet3!$A3,Sheet2!$C$3:$C$319,0),2)),"")
Answers Post
1 Guest(s)
