combine vlookup formula in one row

SkiPatrolScott

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

Victoria_Indimar

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

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

SkiPatrolScott

solved with INDEX MATCH, adding color condition for easy reference

Genevieve P.

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

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-a-matching-value

Victoria_Indimar

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)

SkiPatrolScott

@Victoria_Indimar

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

Victoria_Indimar

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