Cross referencing Sheet-formulas

Question

Hi

I want to look up the requestor name ,Department rep,Department Rep(second if applicable) for each Tracking ID from another sheet which contains the names of these department reps.

If a department name is entered the corresponding column should return the name of the rep from the other sheet.

Which is the best function to use:

-INDEX/MATCH

-VLOOKUP

-IF Formula so it can return only that dep rep name?

Pls advise how this could be achieved.

Thanks

Ranjini Walter

Christopher Pretty · Accepted Answer

Personally, I would use an Index/Match formula to do this. That way if the columns ever change places on the sheet the formula won't break (this could happen with a vlookup since it is counting the columns).

Andrée Starå · Accepted Answer

Hi @Ranjini Walter

I hope you're well and safe!

I agree with Christopher.

INDEX/MATCH is, in most use cases, the better option because it's more secure when you update/move things around, but also in the way that nobody can modify to view something they shouldn't be able to see.

The structure would look like this.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row, 
{ColumnWithTheValueToMatchAgainsTheCell}, 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!