If & Vlookup mixed formula only partly working

1. 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.  Register To Reply

2. 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.

FALSE turned if the IF tests fail so check data.  Register To Reply

3. 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.  Register To Reply

4. 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"  Register To Reply

5. 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?  Register To Reply

6. Re: If & Vlookup mixed formula only partly working

Post a file.  Register To Reply

7. 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.  Register To Reply

8. 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  Register To Reply

9. Re: If & Vlookup mixed formula only partly working Thanks for the information.  Register To Reply

10. 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  Register To Reply

11. 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.   Register To Reply

12. 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)  Register To Reply

13. 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.  Register To Reply

14. 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).  Register To Reply

15. Re: If & Vlookup mixed formula only partly working

That wasn't suggested as a 'solution'

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 "  Register To Reply

16. Re: If & Vlookup mixed formula only partly working

Repost your file showing the formula AND expected results.  Register To Reply

17. 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?  Register To Reply

18. 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.  Register To Reply

19. 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?   Register To Reply

20. 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))  Register To Reply

21. 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?  Register To Reply

22. Re: If & Vlookup mixed formula only partly working

Without having the data .....????  Register To Reply

23. Re: If & Vlookup mixed formula only partly working

Apologies - thought i'd added the sheet.

Lets try that again  Register To Reply

24. Re: If & Vlookup mixed formula only partly working

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

25. 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.  Register To Reply

26. 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.  Register To Reply

27. 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   Register To Reply

28. 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.  Register To Reply

29. 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.  Register To Reply

30. Re: If & Vlookup mixed formula only partly working

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

31. 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.  Register To Reply

32. 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))}  Register To Reply