jkeppel
Level 1 (4 points)
iWork

Q: Compare columns

I need to compare two columns (SKU) on different sheets. When a number matches the price column's corresponding cell should be entered onto the first sheet in a new column labeled price.

 

I have tried using LOOKUP and that seemed to work, as some of the results were noted as 'couldn't find the requested value', but others had a price entered and when I randomly was checking I found many that were incorrect. I don't understand how a price was entered if there wasn't a match from the other sheet.

 

Also, I'm wondering if I should be using a different formula.

 

Thank you for your help.

Posted on Aug 20, 2016 12:00 PM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (5,063 points)
Mac OS X
A:

Hi jkeppel,

 

I think I threw you off calling my lookup table "Lookup". There are only the two functions INDEX and MATCH.

 

An easy way to build formulas in Numbers is to point and click. It will take care of the syntax for you.

So when I type INDEX I get this:

Screen Shot 2016-08-20 at 10.26.39 PM.png

range is the column you want your values to come from. Navigate to the proper sheet and table and click the correct column. I think it is "F".

Match is going to find your row, so click on row-index and type MATCH.

Screen Shot 2016-08-20 at 10.31.20 PM.png

With search-for highlighted click on the value you are matching. This is in the same row that the formula is in.

search-where navigate to the table you are retrieving the prices from and click on the SKU column.

matching-method is "find value".

We don't need column-index or area-index so you can ignore them.

 

Let me know if this does it for you (or not).

 

quinn

Posted on Aug 20, 2016 9:37 PM

Close
jkeppel

Q: Compare columns

  • All replies
  • Helpful answers

  • by t quinn,Helpful

    t quinn t quinn Aug 20, 2016 4:12 PM in response to jkeppel
    Level 5 (5,063 points)
    Mac OS X
    Aug 20, 2016 4:12 PM in response to jkeppel

    Hi jkeppel,

     

    LOOKUP() can be very useful but it will return a close match.

     

    I generally use INDEX/MATCH to do all my lookups. You can restrict it to exact match (find value) and it is actually easier than VLOOKUP() or HLOOKUP(). It will also return an error if you set it to match a value that is not in your lookup table.

    Screen Shot 2016-08-20 at 3.34.36 PM.png

    C2= INDEX(Lookup::B,MATCH(B2,Lookup::A,0))

     

    quinn

  • by jkeppel,

    jkeppel jkeppel Aug 20, 2016 4:12 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Aug 20, 2016 4:12 PM in response to t quinn

    Quinn-

    Thank you for your response.

    I do still need assistance. When I insert the index function I get four prompts to fill. I have not been able to get the Lookup::B  in its own capsule. I get the Lookup function inserted then more prompts. Same with Match.

     

    I'm entering the function in I2 and it will get copied down 3200 rows.

     

    08-20 -16.jpg

     

    Some of the numbers in col B will match the numbers in col G Sheet 2015-2016 Hosiery. When they match the corresponding price (col F) needs to be entered into col I.

     

    08-20 -16a.jpg

     

    I'm still trying to get your formula to work for me. I understand it with your example Thanks again and hopefully these images will help.

  • by SGIII,Helpful

    SGIII SGIII Aug 21, 2016 2:15 PM in response to jkeppel
    Level 6 (10,796 points)
    Mac OS X
    Aug 21, 2016 2:15 PM in response to jkeppel

    I think quinn has a rare Sunday off so he couldn't get to your followup question.

     

    The syntax for MATCH is:

     

    MATCH(search-for,search-where,matching-method)

     

    In this case the 'search-for' is column B of your first table.

    The 'search-where' is column G in the second table.

    For INDEX in this case you only need to have column F of the second table. Delete the "extra" parameters suggested by the Formula Editor.

     

    So you could do something like this (not tested here):

     

    =INDEX(<column F of second table>,MATCH(B,<column G of second table>,<find value>))

     

    SG

  • by t quinn,Solvedanswer

    t quinn t quinn Aug 20, 2016 9:37 PM in response to jkeppel
    Level 5 (5,063 points)
    Mac OS X
    Aug 20, 2016 9:37 PM in response to jkeppel

    Hi jkeppel,

     

    I think I threw you off calling my lookup table "Lookup". There are only the two functions INDEX and MATCH.

     

    An easy way to build formulas in Numbers is to point and click. It will take care of the syntax for you.

    So when I type INDEX I get this:

    Screen Shot 2016-08-20 at 10.26.39 PM.png

    range is the column you want your values to come from. Navigate to the proper sheet and table and click the correct column. I think it is "F".

    Match is going to find your row, so click on row-index and type MATCH.

    Screen Shot 2016-08-20 at 10.31.20 PM.png

    With search-for highlighted click on the value you are matching. This is in the same row that the formula is in.

    search-where navigate to the table you are retrieving the prices from and click on the SKU column.

    matching-method is "find value".

    We don't need column-index or area-index so you can ignore them.

     

    Let me know if this does it for you (or not).

     

    quinn

  • by jkeppel,

    jkeppel jkeppel Aug 21, 2016 2:16 PM in response to SGIII
    Level 1 (4 points)
    iWork
    Aug 21, 2016 2:16 PM in response to SGIII

    Thank you.

  • by t quinn,

    t quinn t quinn Aug 21, 2016 2:18 PM in response to jkeppel
    Level 5 (5,063 points)
    Mac OS X
    Aug 21, 2016 2:18 PM in response to jkeppel

    Hi jkeppel,

     

    Glad to help and thatnks for the check!

     

    quinn

  • by jkeppel,

    jkeppel jkeppel Aug 21, 2016 2:35 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Aug 21, 2016 2:35 PM in response to t quinn

    quinn-

     

    Thank you!  This works. much appreciated.

  • by jkeppel,

    jkeppel jkeppel Aug 21, 2016 2:36 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Aug 21, 2016 2:36 PM in response to t quinn

    quinn-

     

    This works! Thank you so much.