Cross referencing Sheet-formulas

Ranjini Walter

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

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å

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!

Christopher Pretty

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å

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!

Ranjini Walter

Thank you Chris and Andrée ...I was also thinking about INDEX/MATCH...but my only concern was that If a different dep name is entered will it give the corresponding Rep's name?

I am going to try the formula .Will reach out to you for further help in this:)

Thanks

Ranjini Walter

Ranjini Walter

Hey Chris and Andree...I wanted to have a formula for a scenario when a department is selected from a drop down it should auto-populate the corresponding Dep rep name:

So if I selected SS&P it should return Jack and Compliance is selected it should return a different name....for that particular Tracking ID

Pls advise how this can be achieved.

Thanks

Ranjini Walter