O365 Structured Reference throwing #REF! error

[kupdegrove]

This one has me COMPLETELY stumped. Using two identical structured references, the first works beautifully and the second throws a #REF error. I have tried everything I can find to no avail. The source sheets are different but there is nothing special about either. These two formulas are in columns right next to each other in the same worksheet and both source worksheets are in the workbook

=INDEX(tbl_vips,MATCH([@Name],tbl_vips[VIP],0))

Correctly returns the corresponding VIP column from tbl_vips

=INDEX(tbl_builds,MATCH([@Name],tbl_builds[Computer Name],0))

Correctly returns #N/A when the MATCH fails but returns #REF! when there's a Match (confirmed via manual comparison) Naturally, the error pop-up indicates "Invalid Cell Reference" so it's not finding tbl_builds[Computer Name] for some reason

Any help would be greatly appreciated as this is driving me nuts.

 

[Fluff]

Hi & welcome to MrExcel.
How many columns do you have in both those tables?

 

[kupdegrove]

Hi & welcome to MrExcel.
How many columns do you have in both those tables?

Thank you! tbl_vips has a single column (just a lookup table). tbl_builds has 2 (computer name and OS) Although I only need the name.

 

[Fluff]

That's the cause of your problem, with the two column table you need to specify which column to return.
If you just want to return the same value you are looking up, then try

=INDEX(tbl_builds[Computer Name],MATCH([@Name],tbl_builds[Computer Name],0))

 

[kupdegrove]

That's the cause of your problem, with the two column table you need to specify which column to return.
If you just want to return the same value you are looking up, then try

=INDEX(tbl_builds[Computer Name],MATCH([@Name],tbl_builds[Computer Name],0))

Doh! Rookie mistake. I know that too :-/ That fixed it. Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback.