# 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

• 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)

• 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

• solved with INDEX MATCH, adding color condition for easy reference

• Aha! Index(Match is one of my favourite combos. Glad to see you sorted it out 🙂

• 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)

• Thanks, question what does the "},0)1)" at the end of the index formula do, different then how I have it as "},0))"

• 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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!