Excel Formulas

  1. What is Vlookup used for?
    • -when you want to lookup and retrieve data from multiple spreadsheet
    • -for example if i need UPCs in a new product template im working one
  2. Vlookup formula
    =VLOOKUP(lookup value, table array, column index, FALSE)

    • lookup value = what are you looking for? Ex. SKU
    • table array = Where are you looking?
    • Ex. UPC spreadsheet
    • column index = What do you want to return?
    • Ex. "3" for 3rd column that has UPC numbers
    • FALSE=exact match of what i want returned
  3. Formula counts the number of characters in a cell
    =LEN(A1)


    Used this for ebay title updated, needed to know the characters to optimize our titles
  4. IF statement formula that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota”
    IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)
  5. Formula if you need to combine data into one cell.
    =CONCATENATE(A1," ",B2)
  6. Formula to output the largest numerical value in the rows
    =MAX(A1:A10)
  7. INDEX & MATCH FORMULA
    =INDEX(B2:F5,MATCH(D8,A2:A5,0),MATCH(D9,B1:F1,0))

    -cross match what a particular SKU sold for a given month
  8. IF Formula
    where i compared our MAP with Wayfair MAP
    =IF(A2=B2,"Match","Break")


    -Ex. Match MAP with Wayfair MAP to find if there are any violations
  9. Formula where i compare our inventory export with channel export to see what SKU is missing on the channel
    =COUNTIF(Sheet2!A:A,A2)>0

    A:A is the column list you check value in, and B2 is the value you want to find if is in the list.
Author
dante01
ID
348068
Card Set
Excel Formulas
Description
Excel
Updated