Equation to: Look up value from another sheet and return a new value

Karenmst

Hi. I have 2 master data sheets (Sheet A and Sheet B). Both sheets have a column called [Company Name]. On Sheet A, i need an equation that looks at [Company Name] in Sheet A, then searches for a match in Sheet B. If there is a company name match in Sheet B, then I would like to return the value of a different column [Company Type] from Sheet B. If there is no match, then I would like to return a blank (or a default text e.g. "No Match").

Thanks in advance!

Paul Newcome

Try this:

=INDEX({Sheet B Company Type}, MATCH([Company Name]@row, {Sheet B Company Name}, 0))

Paul Newcome

Now that your INDX/MATCH is working, you can wrap it in an IFERROR. I generally do this AFTER making sure the base formula is working because the IFERROR can make troubleshooting a bit of a pain.

=IFERROR(INDEX(……., MATCH(…………………)), "Text of choice")

You can also remove the words "Text of choice" and just leave the quotes to output a blank.

Paul Newcome

Try this:

=INDEX({Sheet B Company Type}, MATCH([Company Name]@row, {Sheet B Company Name}, 0))

Karenmst

That works for returning the right value when there is a match! but when there is no match (e.g. Company name is in Sheet A, but not in Sheet B), then the equation returns "#NO MATCH". In this case I'm hoping to have it return either a blank OR a specific text that I can specify (e.g. "No Company Type Available").

Would you know how to add this additional feature to the Index / Match equation that is otherwise working?!

Much appreciated :)

Paul Newcome

Now that your INDX/MATCH is working, you can wrap it in an IFERROR. I generally do this AFTER making sure the base formula is working because the IFERROR can make troubleshooting a bit of a pain.

=IFERROR(INDEX(……., MATCH(…………………)), "Text of choice")

You can also remove the words "Text of choice" and just leave the quotes to output a blank.

Karenmst

Thank you! The IFERROR wrap did the trick! I'll need to remember this one since this will come in handy often. Much appreciated!

Paul Newcome

Happy to help. 👍️