combine vlookup formula in one row

Options

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 ✓
    Options

    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 ✓
    Options

    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!