combine vlookup formula in one row

This may be two questions in one, as match index may be a better solution but right now I only know how to use VLOOKUP.

I have two sheets pulling data from other sheets, one tracks number of routes run as Lead or Second for a particular route; the other tracks the patroller QUAL for each route ' L, S, T, -,'

I would like to combine the results of Route Counts (3L - 1S) and Quals ( L ) into one row, ' 4 - 2 L ' with the intent of conditional formatting so that route builders can see a name/route/color to determine the position when building teams.

=VLOOKUP(Patroller@row, {RouteCounts}, 4, false) Sheet 1, the highlighted cell is [Patroller]

=VLOOKUP(Patroller@row, {RouteQuals},2, false) Sheet 2, the highlighted cell is [Patroller]

both work independent, but I cant combine? I do need to add a "/" or " " in between the formulas


Essentially I would like to produce one sheet that shows the count with the qual.

The above sheets are still under construction

Best Answers

  • Victoria_Indimar
    Victoria_Indimar ✭✭✭✭✭
    Answer ✓

    Hello! Yes you can combine the output of those two formulas in one cell. It'll depend on how you want the combined result to look but you could do something like:

    =VLOOKUP(example) + " - " + VLOOKUP(example2)


    I would also recommend using the INDEX-MATCH instead. Just replace your VLOOKUP formulas with this:

    VLOOKUP(Patroller@row, {RouteCounts}, 4, false) --> INDEX({RouteCounts_output column},MATCH(Patroller@row, {RouteCounts_Patroller column},0),1)

    VLOOKUP(Patroller@row, {RouteQuals},2, false) --> INDEX({RouteQuals_output column},MATCH(Patroller@row, {RouteQuals_Patroller column},0),1)

  • Victoria_Indimar
    Victoria_Indimar ✭✭✭✭✭
    Answer ✓

    It is the Column Index which is optional so how you have it is the same if you are selecting a single column range. :)

    https://help.smartsheet.com/function/index

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!