# 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

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

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

• ✭✭✭✭
Options

solved with INDEX MATCH, adding color condition for easy reference

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

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

• ✭✭✭✭
Options

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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!