If value in table X not in table Y then return it - how to?

[Camel123]

Hi,

I have one table (1) in sheet 1 and another table (2) in sheet 2. I want a formula in sheet 3 that checks if values in table 2 exists in table 1, if no, then the formula should return the missing value.

Any suggestions?

 

[James006]

Glad you could fix your problem ...

 

[Camel123]

Unfortunately not fixed in in my workbook that is suppose to be used I believe it might have to do with numbers in one table being considered as numbers and in the other table not. Used "Isnumber" and it showed both false and true. THough both sections are formatted as "general", the difference is the numbers in one table being generated by formulas and the numbers in the other table being pasted from another workbook so no formula.

 

[James006]

What you are describing in terms of sources of data feeding the tables ... should have no impact on your match formula ...:wink:

 

[Camel123]

Found the error... the same series of numbers were categorized as "numbers" in one sheet, and "text" on the other sheet. I could only convert the "text" ones by adding "VALUE" in the formula. THen the INDEX started working.

 

[James006]

Congrats on your perseverance ... :wink:

 

[Camel123]

And INDEX MATCH does not work with text's, am I correct? Unless you combine it with something else?

 

[James006]

No ... No ... Index Match does work with Text too ...

 

[Camel123]

I am so frustrated at the moment on this f*cking Excel... When converted all values using the VALUE formula, INDEX MATCH worked perfect. I replaced VALUE with TEXT(cell reference;"0") but then it turned out in errors only... I think i'll check out for today my brain is overloaded atm. Maybe I need to rest to find the probably simple cause.

 

[James006]

A pause is sometimes ... absolutely necessary ... !!!

If you have the opportunity to post a tiny sample file on a cloud platform ...