Comparing values betwen sheets and returning values

Hi,
I am getting really confused using the match address and indirect statements in a formulae.
I have attached an excel example of what I am trying to achieve and I'm hoping someone can assist.

I have two worksheets EUC and DV:
Worksheet EUC contains a column of asset numbers (column 1)
Worksheets DV contains a range of assets held by a person. The person's name is column A and the asset numbers are in columns B, C, D, E, F.

What I am trying to do is beside each of the Asset Numbers in EUC place the name that corresponds to that number from sheet DV Column 1,
In the DV asset register there is a total of 4 assets that a person can hold.
If they don't hold the asset then the cell is blank otherwise it has a number in it.

I have tried using the MATCH function but am getting an error.
The Row 1 show the headers of what I am trying to get.
I want to use this as a learning example.

If anyone can help I would greatly appreciate this as currently I have to do each asset number manually using copy and find. There is a lot of assets.

Cheers Tony

Hi I've updated the example file to demonstrate that the values do exists (added an exists column)
Cheers tony

UPDATED THE SPREAADSHEET WITH FURTHER INFORMATION - THANKS
I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496) 64-bit

Power Query Solution

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Please show in the sheet of Post #2 in row 2 (column C until G) the expected results of the first item

Thanks for adding the example.

The values in D2-G2 are clear for me.

I've a still question about match (C2)

Is match in C2 the same as Row of Match?
Or is match the value behind Asset in the header (sheet DV!C1)?

Both are 2.

Another question!

Are you still using Excel 2016?

You can check your current version of MS Office in Excel. Click file --> click account.

The 3 most recent versions are 2019, 2021 and 365.

That is nice. Please update your profile.

Here is one formula for the whole table (column B to H) in one go (no copy to the right or down needed).

Please empty all expected results and try in B2:

Please Login or Register  to view this content.

To better test the formula regarding the addresses, I moved the DV table 1 column and 4 rows, so that I can be sure that it works properly.

Power Query is not an add in for your version of XL. It found on the Data Tab of the Ribbon and "Get and Transform Data."

Power Query is a built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.


- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Here is a solution with separate formulas:

Please try in B2 and copy down:

Please Login or Register  to view this content.

in D2 and copy down:

Please Login or Register  to view this content.

in E2 and copy down:

Please Login or Register  to view this content.

in F2 and copy down:

Please Login or Register  to view this content.

in G2 and copy down:

Please Login or Register  to view this content.

in H2 and copy down:

Please Login or Register  to view this content.

A solution with INDEX/MATCH,

please try in G2 and copy down:

Please Login or Register  to view this content.

and try in H2 and copy down:

Please Login or Register  to view this content.

and try in B2 and copy down:

Please Login or Register  to view this content.

It is not a good idea (understatement) to look for a solution through the functions ADDRESS and INDIRECT.
This is very slow and complex.

These features should be avoided and only used when there are no other options.

You are Welcome!

Thanks for the feedback. Glad to have helped. .

If there any questions left, don't hesistate to ask these questions here in this thread.

If you haven't done it yet, please consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution.

There is also no TOCOL in Excel 2013 and 2016.
That is why it is easiest to convert the formulas from Post #15 for Excel 2013 and 2016.

Solution for Excel 2007 and newer:

Please try in B2 and copy down:

Formula:

Please Login or Register  to view this content.

In D2 is no difference

Please try in E2 and copy down:

Formula:

Please Login or Register  to view this content.

in F2 and copy down:

Formula:

Please Login or Register  to view this content.

in G2 and copy down:

Formula:

Please Login or Register  to view this content.

and in H2 and copy down:

Formula:

Please Login or Register  to view this content.