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

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 INDEXMATCH 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. :)
Answers

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 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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 INDEXMATCH 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. :)
Help Article Resources
Categories
Check out the Formula Handbook template!