Forum Discussion
VLOOKUP returning #N/A when using text
VLOOKUP works from left to right, i.e. lookups the value in left most column and if returns the value in matched row from the column in next parameter.
In your case works, for example, INDEX/MATCH
=IFNA( INDEX( INDEX( AllCards, 0, MATCH(TestDeckMain[[#Headers],[ATTRIBUTE]], AllCards[#Headers], 0) ), MATCH(TestDeckMain[@[NAME]:[NAME]], AllCards[[NAME]:[NAME]], 0 ) ), "" )Please check in attached.
You have to allow others to view the workbook - I now get "Sorry, an error has occurred".
- Strange, it said anyone with the link can edit. I've added an attachment HansVogelaar
Thanks. VLOOKUP searches for the lookup value in the 1st column of the lookup range, but the name is in the 4th column.
Option 1:
In D2:
=IF($C6<>"",VLOOKUP(TRIM($C6),AllCards[[NAME]:[DEF]],2,FALSE),"")
Note that the column index has shifted.
Option 2, if you have Microsoft 365 or Office 2021:
=XLOOKUP($C6,AllCards[[NAME]:[NAME]],AllCards[ATTRIBUTE],"")
- Thanks, this worked pretty well too. Unfortunately no 365 or retail package to use XLOOKUP, but it's good to see there's always a way around
VLOOKUP works from left to right, i.e. lookups the value in left most column and if returns the value in matched row from the column in next parameter.
In your case works, for example, INDEX/MATCH
=IFNA( INDEX( INDEX( AllCards, 0, MATCH(TestDeckMain[[#Headers],[ATTRIBUTE]], AllCards[#Headers], 0) ), MATCH(TestDeckMain[@[NAME]:[NAME]], AllCards[[NAME]:[NAME]], 0 ) ), "" )Please check in attached.
- This is great, thanks! It took me a while to read into INDEX and MATCH, but I can see how versatile those functions can be 🙂
