Value from tables returns #Value!

[MRDBS]

Hello Excel Collective

I am new to tables, I usually go Willy-Nilly and do not format my data to tables.
Usually that does work for me without any big problems.

Sadly my unwillingness to work and learn tables placed me into a situation which I feel quite difficult to find info on.
I am now working with a MS Forms and the data from that form comes formatted as a Table.

Simple stuff like referring to a value in cell in a table does not work and i get the #Value! error.

So if i write

	A	B
1	=sheet2(a1)	=sheet2(b1)
2	=sheet2(a2)	=sheet2(b2)

Instead it shows

	A	B
1	=Table1[@HeaderColumnA]]	=Table1[@HeaderColumnB.:]]
2	=Table1[@HeaderColumnA]]	=Table1[@HeaderColumnB.:]]

and in turn gives me the error #Value! in the cells (A1;A2;B1;B2)
For some reason though it does not do it always, I have some cells where it manages to extract and use the value from the cell in the table.

Right now I struggle with this:

=XLOOKUP(Table1[@[HeaderColumnA]];'Sheet1'!$A:$A;'Sheet1'!$B:$B)

This also only returns: #Value!

While I have another function that is identical and works:

=XLOOKUP(Table1[@[HeaderColumnA]];'Sheet1'!$A:$A;'Sheet1'!$C:$C)


I feel silly as it does not make sense.

 

[windies17]

HI MRDBS, when referencing an xlookup, what are you trying to lookup?.

 

[MRDBS]

HI MRDBS, when referencing an xlookup, what are you trying to lookup?.

I am looking for a value entered into the MS Forms, which should be found in a dataset with a correlating value which i want to display.

User enters value "AB12" into the MS Forms, the function then looks for value "AB12" in the array A:A in another sheet, if found it shows the correlating value of array B:B

 

[MRDBS]

Bump

 

[RoryA]

In this syntax:
=Table1[@HeaderColumnA]
the @ symbol means 'this row' so if you use that formula in a row that does not intersect your table, you will get a #VALUE! error. That in turn would cause other formulas, like XLOOKUP, that use that reference to also return errors.

 

[MRDBS]

In this syntax:
=Table1[@HeaderColumnA]
the @ symbol means 'this row' so if you use that formula in a row that does not intersect your table, you will get a #VALUE! error. That in turn would cause other formulas, like XLOOKUP, that use that reference to also return errors.

Hi thanks for your feedback.
How would i then reference the value of a cell in a table instead?

Because what I did now is I write my formular: =xlookup( "then I click the cell I want to reference from the table", then the array where to look for it, then the array of the value I want to display)

 

[RoryA]

Is the cell in the table in the same row as the formula cell? If so, it should work (although the original syntax you posted was not valid as you had mismatched square brackets)

 

[MRDBS]

Is the cell in the table in the same row as the formula cell? If so, it should work (although the original syntax you posted was not valid as you had mismatched square brackets)

For some strange reason, I now reopened the File and it found the value and the error is no more.

You are right I seem to have removed a bracket when i change the name, but in the file it was still correct.

thank you for taking the time to reply, I will close this post for now.