Index and Match between sheets providing Invalid value

Aiva

Hi All,

I have a master data sheet collating uniform info + delivery addresses. All fields work but for some reason half of delivery details come as #INVALID VALUE - Not sure what is the issue or how to resolve.

=INDEX({Uniform site master Range 1}, MATCH([Site SIN number]@row, {Site Details Range 2}, 0))

Aiva

I have ended up redoing the formula, relinking all the columns and it seems to work! not sure why the other one crashed midway… thanks for help and support @Paul Newcome

=INDEX({ADDRESS}, MATCH([Site name]@row, {Site Details Range 1}, 0))

SoS | Dan Palenchar

Hello @Aiva,

What are the column types for:

1. Uniform site master Range 1
2. Site SIN number
3. Site Details Range 2
4. The column your lookup formula is in

If your are pulling in data to the wrong column type this might cause it. If you are referencing dates and there are blanks this can also be a source of error.

Aiva

Hi @Dan Palenchar ,

All the columns are set as Text / Number.

Formula works to half of the sheet and then goes into #invalidvalue although data is provided in originating sheet (although out of 600 invalid value lines, there are few lines that got addresses in)

I also noticed, that some cells are blank even though there is data provided in originating sheet

Any thoughts?

Paul Newcome

Are you able to provide some screenshots for context?

Aiva

hi, @Paul Newcome

This is the end product where I want addresses to pull through to if this helps.

Essentially, this sheet has got unique site numbers and unique pin number for employees where info is on other sheets and if matching pull the delivery address provided for the site against that employee

example.jpg

Paul Newcome

So it looks like the INDEX/MATCH is working for some columns but not others? Is that correct?

How exactly are you populating the source data for those columns that are coming in with the error?

Aiva

@Paul Newcome that's correct, half of the sheet pulled without issues for this particular column, and then from about row 600 down #invalid value.

The data was filled in via dynamic view (for site number + address) by managers and forms (for uniform sizes from employees). Managers had a sheet via dynamic view to update delivery details for each specific site. We had an exercise where employees had to be allocated to their work locations by managers. Then if that same site is picked up against employee, it should pull delivery data manager provided for that site number (they had to select site name from drop down which makes it unique and avoids errors) - making it a super sheet with all data linked from various data collation exercises

I have also got sheets with site numbers / employee numbers that are from our system as true version of facts and need to match information before they get linked into my end product sheet.

Not sure if I am over complicating this?

Paul Newcome

Is it half of the columns in every row or half of the rows in every column that are throwing the error?

Aiva

I have ended up redoing the formula, relinking all the columns and it seems to work! not sure why the other one crashed midway… thanks for help and support @Paul Newcome

=INDEX({ADDRESS}, MATCH([Site name]@row, {Site Details Range 1}, 0))