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

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.

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.

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"

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?

Post a file.

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.

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

9. ## Re: If & Vlookup mixed formula only partly working

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

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

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.

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)

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.

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

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 "

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

Repost your file showing the formula AND expected results.

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?

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.

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.

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))

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?

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

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

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

Apologies - thought i'd added the sheet.

Lets try that again

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

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

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.

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.

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

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.

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.

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

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

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.

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))}

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

#### 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