Can any one Please...
VLOOKUP with TRUE/FALSE by ignoring #NA AND index match too. for the below example
1 8 true 1
2 5 #N/A #N/A
3 6 #N/A #N/A
4 4 true 4
5 5 true 5
6 1 true 6
3rd Column = =IF(VLOOKUP(A2,$B$1:$B$6,1,0),"true"). For this i want to replace #NA with "FALSE"
4th column = =INDEX($B$1:$B$6,MATCH(A2,$B$1:$B$6,0)) . For this i want to replace #NA with "FALSE"
=IF(ISERROR(VLOOKUP(A2,$B$1:$B$6,1,0)),"False","true")=IF(ISERROR(INDEX($B$1:$B$6,MATCH(A2,$B$1:$B$6,0))),"False",INDEX($B$1:$B$6,MATCH(A2,$B$1:$B$6,0)))
Please Login or Register to view this content.If it is not working pls attached sample excel filePlease Login or Register to view this content.
Last edited by samba_ravi; 09-18-2015 at 03:24 AM.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Deleted post.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
4TH COLUMN : as requested working fine (false)....can we replace the values with true statement.
Eg :
1 (as true)
False
False
4 (as true)
5 (as true)
6 (as true)
=IF(ISERROR(VLOOKUP(A2,$B$1:$B$6,1,0)),"False",VLOOKUP(A2,$B$1:$B$6,1,0))Please Login or Register to view this content.
Hi nflsales..
can u Please look into Post #4
=IF(COUNTIF($B$1:$B$6,A2),"True","False")=IF(ISERROR(MATCH(A2,$B$1:$B$6,0)),"False","True")Please Login or Register to view this content.Please Login or Register to view this content.
Just a note:
=ISERROR() will ignore any error that comes back. If you specifically want to check for #N/A you could use
=IF(ISNA(),[true part],[false part])
May not be completely significant in this question, but still worth mentioning imo.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks