+ Reply to Thread
Results 1 to 32 of 32

If & Vlookup mixed formula only partly working

  1. #1
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    If & Vlookup mixed formula only partly working

    Hi all,

    The below formula is working on about 40% of the sheet i'm using it on - for the rest the result is 'False'. Can someone explain why it's only partly working?

    =IF(B18='Receipted qty'!A18,IF('bill qty'!C18='Receipted qty'!C18,VLOOKUP(B:B,'Receipted qty'!A:D,4,0)))

    I've manually checked the results and it should be bringing back a number (like the rest did), as the answer is within it's boundaries.

    Bizarrely i'd understand if it did not work at all (got formula wrong), but only half working has thrown me

    Any help would be greatly appreciated.

    Cheers.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    Without any data it's impossible to say what is wrong. Please post a sample file (not image) showing expected results.

    To upload file, click "Go Advanced" then "Manage Attachments"

    FALSE turned if the IF tests fail so check data.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: If & Vlookup mixed formula only partly working

    =IF(B18='Receipted qty'!A18,IF('bill qty'!C18='Receipted qty'!C18,VLOOKUP(B:B,'Receipted qty'!A:D,4,0)))

    this part (red text) won't work, you need to select a cell and not a range.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    If it's returning FALSE, then one of these two statements is false
    B18='Receipted qty'!A18
    'bill qty'!C18='Receipted qty'!C18

    In your formula, you did not tell Excel what to do if either of those are false so it just responses "False"
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Many thanks for the qucik responses - i've changed the cell range to a single cell and the result is still false.

    Why i'm confused is that is shouldn't be false as when i scan through the sheet i'm looking at i can see all the info there?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    Post a file.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    @Oeldere, you can in fact use VLOOKUP(B:B, A:D,4,0) It will look in the same row as the formula is in which is usually what people are trying to do anyway. I would worry that it was pulling the wrong data. In any case, It probably is not a very efficient way of doing things and is a little confusing.

    The reasons I gave are the only reasons it could be returning false.

  8. #8
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Hopefully this works.

    the idea being looking for the info at the top file, from the bottom file using that formula
    Attached Files Attached Files
    Last edited by Weaselwithagun; 04-27-2016 at 09:07 AM.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: If & Vlookup mixed formula only partly working

    Please Login or Register  to view this content.
    Thanks for the information.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    Well, you didn't include any formulas but many of your numbers are stored as text. They will not match a number stored as a number. To convert those values to numbers, Select the column (A) and Data Tab> "Text to Columns", then click "Finish" That should fix it

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    Thanks for the information.
    I've gleamed a lot more information from you over the past few years. Thank you.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If & Vlookup mixed formula only partly working

    ChemistB has hit the problem on the head in post #4.
    If it's returning FALSE, it has nothing to do with the VLOOKUP.
    One of your IF statements is FALSE.

    Try changing the formula to

    =IF(B18='Receipted qty'!A18,IF('bill qty'!C18='Receipted qty'!C18,VLOOKUP(B:B,'Receipted qty'!A:D,4,0),"A"),"B")

    What do they return now (the ones that previously returned FALSE)

  13. #13
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    The above formula does work (in that it gives either A or B as an answer), but this is not what i'm looking for - so i suspect i'm going about this the wrong way :S

    I wanted a formula that would show me the desired number by it's corresponding previous numbers - so in the original sheet it would check that colum b's number matched, then that column c's also matched and then show the resulting final number in column D.

    Can someone suggest a different formula/technique to try please?

    Many thanks.

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    Did you convert the non-numeric numbers to numbers as I suggested? You're going to have to upload an example spreadsheet with your formula (or something similar) in it showing it failing and telling us what you would expect in it's place (instead of FALSE).

  15. #15
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If & Vlookup mixed formula only partly working

    That wasn't suggested as a 'solution'
    It was a Troubleshooting Step, to help you find the problem..

    So, if that formula returns A, it means
    'bill qty'!C18 DOES NOT EQUAL 'Receipted qty'!C18

    If that formula returns B, it means
    B18 DOES NOT EQUAL 'Receipted qty'!A18


    You need to check those cells for exact spelling, extra spaces like "Hello" vs " Hello" or "Hello "

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    Repost your file showing the formula AND expected results.

  17. #17
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Apologies, i should have given an example:

    3590198905 3593054303 KCBR5412 FALSE
    3590198905 3593054303 KCBR5420 FALSE
    3590198905 3593054303 KCLR5410 FALSE
    3590198905 3593054303 KCLR5420 FALSE

    So this is the data i'm trying to match against (where it says false is where the formula is).

    The below table is where it's checking

    3593054303 3590198905 KCBR5420 1,397.995
    3593054303 3590198905 KCBR5416 430.202
    3593054303 3590198905 KCBR5412 266.169
    3593054303 3590198905 KCLR5420 1,852.994
    3593054303 3590198905 KCLR5410 131.699
    3593054303 3590198905 KO14101SLING5M 0

    (just a small example to show my point) - the top table's formula should be looking to match column 1 with column 2 on the bottom, then match top table's column 3 with lower's column 3 & if they both match show the info in column 4.

    Suggestions as to why it's not worked in this example?

  18. #18
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    Okay, your formula doesn't work because the IF statements are only working with one line at a time and you want to pull whole arrays or ranges back that match your individual criteria. One way to do this is with an ARRAYED Formula which is entered with CNTRL + SHFT + ENTER instead of a simple ENTER.

    I put your second set of data into sheet2 and put my formula in Column E of sheet1.

    In E1 copied down, enter this ARRAYED formula
    =INDEX(Sheet2!$D$1:$D$6, MATCH(A1&C1, Sheet2!$B$1:$B$6&Sheet2!$C$1:$C$6,0))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    See attached.
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Apologies for this - but i've used the above formula, and updated it with the parameters for my sheet - now it's bringing back the #REF answer. Example below:

    {=INDEX('bill qty'!D2:D831,MATCH(B16&C16,'Receipted qty'!$A$2:$A$831,'Receipted qty'!$C$2:$C$831))}

    I've tried adding the 0 before the final bracket and it tells me that the formula is incorrect.

    Can someone explain this please?

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    Please check the formulae you are given more carefully

    =INDEX(Sheet2!$D$1:$D$6, MATCH(A1&C1, Sheet2!$B$1:$B$6 & Sheet2!$C$1:$C$6,0))
    =INDEX('bill qty'!D2:D831,MATCH(B16&C16,'Receipted qty'!$A$2:$A$831&'Receipted qty'!$C$2:$C$831,0))

  21. #21
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Huge thanks for pointing that out

    Ok so now it's brought back several answes, though unfortunatley i don't recognise the figures its supplying

    In one example it should have brought back an answer of 266.17, instead it's given a result of 1.41

    Can anyone help me with what's happened here?

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    Without having the data .....????

  23. #23
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Apologies - thought i'd added the sheet.

    Lets try that again
    Attached Files Attached Files

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    No formulas in sheets!???? so can we know what you are doing?

  25. #25
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Apologies, i'm having difficulty getting past the companies security when uploading files.

    The attached shows the formula being used next to the results (copy of formula in column K, would be answers showing in column I).

    Thanks again.
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    Okay, it seems strange that with this formula

    =INDEX('bill qty'!D2:D831,MATCH(B16&C16,'Receipted qty'!$A$2:$A$831&'Receipted qty'!$C$2:$C$831,0))

    you are returning a value from a different sheet than the sheet you are using the MATCH on. Are you sure that "Bill qty" shouldn't be "Receipted Qty" ? That would explain why you don't recognize those numbers.

    I assumed that is the case on the attached workbook.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    Excellent observation

    So that correction has worked and it's bringing back the correct answer - however when i copy the formula down the sheet it doesn't work on any of the other lines.

    There's no filter, and the answers are there (in correct format etc), can some wiser being show me where i'm going wrong please?

    I thought initially if i locked the cells from the index range that would work but it just corrupts the formula :S

    {=INDEX('Receipted qty'!D2:D831,MATCH(B2&C2,'Receipted qty'!$A$2:$A$831&'Receipted qty'!$C$2:$C$831,0))}

    Any assistance would be greatly appreciated

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    If you referring to your test file (help.xlsx) you get #N/A because there are no matches.

    To repeat a previous request; unless we have the file you are working on, we cannot help.

  29. #29
    Forum Contributor
    Join Date
    12-08-2015
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    142

    Re: If & Vlookup mixed formula only partly working

    So hopefully this will work - i've taken a chunk out of the report from both sections as an example.
    Attached Files Attached Files

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    I'm out for while: I'll look at this when I return.

  31. #31
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If & Vlookup mixed formula only partly working

    See the attached:

    Look at formula highlighted in yellow (your version) and compare with that in the green: work out what is wrong. All errors are (I'm sorry to say) down to you - basically not understanding or even copying formulae correctly.

    As far as I am concerned this thread should now be marked as solved as the formula given works correctly.
    Attached Files Attached Files

  32. #32
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: If & Vlookup mixed formula only partly working

    You need to anchor your lookup ranges (put $ in front of columns and rows0

    {=INDEX('Receipted qty'!$D$2:$D$831,MATCH(B2&C2,'Receipted qty'!$A$2:$A$831&'Receipted qty'!$C$2:$C$831,0))}

+ 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. conditional candle color but stacking not working and data mixed
    By PaulHOH in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-30-2015, 09:31 PM
  2. VLOOKUP gives Mixed Results
    By wallyclan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-20-2014, 07:48 PM
  3. Vlookup Mixed
    By dmcbrier in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2014, 12:15 PM
  4. [SOLVED] Partly working macro - variable fails if it holds certain data
    By SoulPrisoner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-21-2013, 09:10 AM
  5. [SOLVED] Vlookup mixed with IF, looking at a date.
    By galvinpaddy in forum Excel General
    Replies: 16
    Last Post: 06-28-2012, 07:05 AM
  6. Substitute code partly working
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-19-2010, 04:54 PM
  7. I mixed , help me in VLOOKUP()
    By 3Dmajid in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-11-2008, 06:40 PM

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