VLOOKUP question

Options

Hi everyone,

I have two sheets, the first picture shows the columns in my sheet. It is a large table. Their are only 16 Superintendents, but over 1,000 principals. The second picture is my second sheet, I use this in card view. The idea is that we search a school and poof the super/principal and other contact info appear.

My question is, is VLOOKUP the best way? I'm not sure if I'm doing this right, but it seems like the search_value metric always needs to be the first row/column of the search_range, that means that I'll have to do over 3,000 vlookup formulas... we have an office number and cell number as well that needs to be included.

Answers

  • John Jonassen
    John Jonassen ✭✭✭✭
    Options

    I'm not sure how the rest of your sheets look, but your VLOOKUP is a bit out of sorts, so let's help with that and maybe it might answer your overall question.

    The VLOOKUP is to be structured as such

    =VLOOKUP({Division Contact Table_Update_Here Range 5},[Whatever Single Cell contains the School Name],[The Number of Column in the Range 5 that you want to pull from],FALSE)

    Make sure that the first column in your Range 5 is the name of the School as that is where your lookup is being performed.

    Does this make a bit more sense?

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    Options

    I'd also recommend moving away from using VLOOKUP but instead using INDEX/MATCH. If someone orders your columns, the vlookup will bring the wrong data point.


    =INDEX([Range of data to bring to your sheet], MATCH([Identifier to look for], [Where to look for the identifier], [sort option]))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Lila De Vera
    Lila De Vera ✭✭✭✭✭
    Options

    Thank you both, I think what I'm really trying to find is a way to say if it is this school, than this principal, this phone number, and this cell number. I have over 1,000 schools, so naturally, over 1,000 principals, phone numbers, and cell numbers.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!