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. :)
solved with INDEX MATCH, adding color condition for easy reference
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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. :)
