locked
Excel2k3sp3 formula help needed RRS feed

  • Question

  • Hello,

    I have a file with 2 sheets, 1 sheet is DATA, the other sheet is COMPARISON

    In comparison I have 2 validation tables referencing the data in column A and B in DATA.  Those are working fine.  The adjacent cells, I have tried Index/Match and VLookup, and I have no clue why I can't get this to work.

    In C2 of COMPARISONS there is a formula referencing the data in B2 which is one of the data validation pulldowns.  I am trying to have the match found between COMPARISONS!B2, and its reference in DATA!B*(about a 40 row deep stack of data there) and then fill out the adjacent cells info. 

    I have done things like this before but I cant remember how.

    I have a shareable sample sheet, which is complete BS, just shares the data formatting.  But currently I have this formula, and it doesn't work:

    =VLOOKUP(B11,DATA!A2:H20,3)       The B11 reference is because on this test sheet, I have a ton of notes above it, it would normally be B2.

    =VLOOKUP(B11,DATA!$A$2:$H$20,3)    had this version too, no joy either.

    I also tried it like this:

    =INDEX(DATA!B2:B44,MATCH(COMPARISON!B2,DATA!C2:C44,0),4)

    That was my first and after several versions, I made the others in the dummy sheet.  Which is why some of the cell references are different; made the dummy sheet so I could share it as needed.

    Below are images of the BS sheet I am using to get my formulas right:

    And the formula sheet

    I did notice that should be H21 not H20, but that should not cause a problem as the reference is on Row2.

    This something you guys can help with?

    Sunday, August 13, 2017 10:12 PM