Using VLOOKUP to compare two values

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hello,

    I'm trying to compare two quantities on two different sheets. I've used VLOOKUP in the past on other sheets. This time round I cannot understand how to us it on this query when the data is contained and mixed in one cell.

    Here goes explaining..
    Sheet1, shows we have stock of Mini Speakers in blue, the 'Main SKU', 'Size' and 'Current Stock'
    'Current Stock' is the cell we are trying to compare


    Sheet2 shows the 'SKU', 'Name' and 'Option1'. Option1 contains the values I'm trying to compare... sku":"X-MiniB-S","value":"S","quantity":"1"


    Basically, I need to check that 'Current Stock' matches the value in 'Options1' and return a value if they do not match.

    Hope the above makes sense

    Many thanks

  • Re: Using VLOOKUP to compare two values

    the image is difficult to see and it would be much better to actually upload a sample speadsheet here, then we do not need to re-enter/recreate any data


  • Re: Using VLOOKUP to compare two values

    this is the text in optio1



    so you need to search through that text and find the SKU number and then move to the next instance of the word quantity and extract the number to compare with sheet 1

    so on sheet 2 in option1
    X-MiniB-L say quantity 0

    then you want to compare with sheet1 row 4 (c4) to match the sku and then compare current stock (E4)

    no idea off the top of my head how to do that ....... at the moment will need to think a while - meanwhile hopefully someone can help


  • Re: Using VLOOKUP to compare two values

    Yes that's correct, you explained that a lot better.

    Another option I was thinking, perhaps split the field?

  • Re: Using VLOOKUP to compare two values

    This will very much depend on the data in that sheet option 1


    So I'm trying to find the starting position of the SKU number from sheet1 FIND(C2,Sheet2!$C$2,1) which will gives us the start character of the SKU
    Now the SKU number could be different lengths of characters - so I have used LEN(C2) to find the length of the SKU
    This is where it can fall down
    from the last character of the SKU the quantity amount is 26 characters from the end of the sku - NOW if this is not the same for other examples - this will fail
    and so having found the start of the SKU - I then add the length of the sku and add another 26 characters to extract the quantity

    we may need to search for quantity if 26 changes - that should be possible

    I will add another reply - once played with that

    see attached sheet

    had a play and this should work

    see attached V2

  • Re: Using VLOOKUP to compare two values

    thats a lot more difficult - I guess we would need to find the row the SKU is in and then using that row find the details for the position stuff again -its going to be likely to have a lot of errors , as quite a few assumptions are being made - not sure the best approach - it maybe a VBA solution is needed not may expertise


  • Re: Using VLOOKUP to compare two values

    Thanks etaf, This one has caused me a lot of pain.
    I may try the other route by separating the values in to their own cells and see if Vlookup will work that way.

  • Re: Using VLOOKUP to compare two values

    text to columns and use delimited with a comma and special "
    that will then enable you to just delete unwanted columns and leave SKU and Quanitiy

    just depends if all the other text are the same layout if that works


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!