+ Reply to Thread
Results 1 to 8 of 8

ISNA with multiple vlookup - want to return Yes/No

  1. #1
    Registered User
    Join Date
    09-09-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    ISNA with multiple vlookup - want to return Yes/No

    Hello. I have been given a project that needs to cross reference multiple spreadsheets.

    I have created a (rather messy) long multiple v-lookup that matches dealer numbers, what I am after is the status of customers direct debit payments. I have this working and the ISNA part of the function is returning true/false - but these are the wrong way round as it is currently testing to see if the result is NA.

    I have tried putting "Yes", "No" values at various places in between brackets but always get an error.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula is shown above, the names eg "cindy" etc are data ranges as this made the formula easier to read/write.

    My question is, what can I add to that formula to get a Yes/No return. Have experimented with using match as well, but can only get this working for one sheet. I am sure it's just me mistyping the formula here.

    Thank you for taking the time to read and any help is appreciated
    Last edited by pilot_jp; 09-09-2016 at 07:24 AM. Reason: Edited to put formula in proper tags

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ISNA with multiple vlookup - want to return Yes/No

    It looks like you simply want to know if the lookup value A3 can be found in column 1 of any of the named ranges.

    Try something like this...

    =IF(COUNTIF(INDEX(cindy,,1),A3)+COUNTIF(INDEX(core,,1),A3)+COUNTIF(INDEX(sek,,1),A3)+COUNTIF(INDEX(chf,,1),A3)+COUNTIF(INDEX(nok,,1),A3)+COUNTIF(INDEX(silke,,1),A3)+COUNTIF(INDEX(gbp,,1),A3),"Yes","No")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-09-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    Re: ISNA with multiple vlookup - want to return Yes/No

    Thanks for the fast reply, trying this throws an error code that the value used in the formula is of the wrong data type - these are all numbers being looked up.

    Any ideas what the error might mean?

    The COUNTIF has got me started on a new thread of thought so will experiment with this. Thank you

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ISNA with multiple vlookup - want to return Yes/No

    Quote Originally Posted by pilot_jp View Post
    trying this throws an error code that the value used in the formula is of the wrong data type - these are all numbers being looked up.

    Any ideas what the error might mean?
    Hard to say without seeing it in context.

    Here's an abbreviated sample that demonstrates this.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: ISNA with multiple vlookup - want to return Yes/No

    Try this :
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-09-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    Re: ISNA with multiple vlookup - want to return Yes/No

    That's perfect, typed it in and it works. Thank you very much.

    Tony, also thank you for the effort in creating the simplified scenario there, your example works perfectly, I think because I'm pulling data from spreadsheets that were given to me, they are just badly formatted.

    Will mark this question as solved, again thank you so much for the help

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ISNA with multiple vlookup - want to return Yes/No

    You're welcome. Thanks for the feedback!

  8. #8
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: ISNA with multiple vlookup - want to return Yes/No

    You are welcome and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup to return multiple rows in return
    By Excelhelpss in forum Excel General
    Replies: 1
    Last Post: 06-18-2015, 09:59 AM
  2. [SOLVED] Help with multiple vlookup with if, and(isna())
    By roscoepwavetrain in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-17-2013, 01:43 AM
  3. Replies: 2
    Last Post: 10-21-2011, 01:41 PM
  4. Excel 2007 : Summing multiple IF(ISNA(Vlookup))
    By jseufert in forum Excel General
    Replies: 1
    Last Post: 09-07-2011, 12:42 PM
  5. Excel 2007 : Isna/vlookup
    By owensjb2 in forum Excel General
    Replies: 4
    Last Post: 05-11-2010, 05:45 PM
  6. Excel 2007 : ISNA and Vlookup help
    By owensjb2 in forum Excel General
    Replies: 2
    Last Post: 05-08-2010, 08:06 PM
  7. ISNA vlookup return matched result
    By shovelnose in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2005, 08:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1