have look up data appear once only

[lecxe]

Hi,

When I lookup some reference data, I would like the looked up value to appear only once, in the column even though I might have it listed multiple time. I cannot have it appear multiple times as I need to sum the column.

This is so that I can group multiple items against the same category and still be able to compare the values.

Also the comparisons afterwards, I only want to appear on one line for the same reason.

I cannot seem to explain it any clearer.

See screenshot and attached.

Thanks.

 

[AliGW]

In B3 copied down:

=IF(COUNTIF(A$3:A3,A3)=1,VLOOKUP(A3,$K$13:$L$15,2,0),"")

 

[ETAF]

how about
=IF(COUNTIF($A$3:$A3,A3)=1,INDEX($L$13:$L$15,MATCH(A3,$K$13:$K$15,0)),"")

 

[lecxe]

Thanks you @AliGW and @ETAF for your solutions!

I've used @AliGW as it is was the first post I looked at and have edited it to work for the full problem I had.

 

[ETAF]

you are welcome
it maybe worth adding the version of excel you are using , when asking a question in future
as solutions will be dependant on what version you have and therefore what functions available

 

[lecxe]

you are welcome
it maybe worth adding the version of excel you are using , when asking a question in future
as solutions will be dependant on what version you have and therefore what functions available

No worries, thanks @ETAF

When I signed up it asked me what version I was using, so I assume there was something that showed this.

I'll be converting it to xlookup instead of vlookup later.

Thanks

 

[AliGW]

For XLOOKUP:

=IF(COUNTIF(A$3:A3,A3)=1,XLOOKUP(A3,$K$13:$K$15,$L$13:$L$15),"")

 

[ETAF]

When I signed up it asked me what version I was using, so I assume there was something that showed this.

Doesnt appear to on this forum , which is a shame , as thats so useful with the later versions having so many more functions - Like xlookup, filter etc
I looked at my profile and nothing to update to show version, so i guess if you include with any future posts what version you would like this to solution to work with

I see ali has it in her signature - so i also added mine there

 

[lecxe]

For XLOOKUP:

=IF(COUNTIF(A$3:A3,A3)=1,XLOOKUP(A3,$K$13:$K$15,$L$13:$L$15),"")

@AliGW @ETAF what if I was to both of these in a table? I've tried and cannot seem to get it to work.

 

[ETAF]

same ranges in the table ?
perhaps load a sample sheet here, with the table and headers - so we can see table name and column name
personally i very rarely use tables - i have afew problems with them , but they do have advantages in dynamic data sets

 

[lecxe]

same ranges in the table ?
perhaps load a sample sheet here, with the table and headers - so we can see table name and column name
personally i very rarely use tables - i have afew problems with them , but they do have advantages in dynamic data sets

No worries, see attached. I've added the tables and updated the formula as much as possible.

It still works, but doesn't use table references correctly.

=IF(COUNTIF(A$3:A3,A3)=1,XLOOKUP([@group],Ref[group],Ref[budget]),"")

Trying to figure out how I would do the equivalent of A$3:A3,A3

I've tried what would be logical with table references, but it doesn't work correctly.

 

[AliGW]

This works in your workbook:

=IF(COUNTIF(A$3:[@group],[@group])=1,XLOOKUP([@group],Ref[group],Ref[delta from budget]),"")

 

[lecxe]

This works in your workbook:

=IF(COUNTIF(A$3:[@group],[@group])=1,XLOOKUP([@group],Ref[group],Ref[delta from budget]),"")

Thanks @AliGW

Is it possible to replace the A$3 with a table like formula?

What is the equivalent.

 

[AliGW]

Try this:

=IF(COUNTIF(Main[[#Headers],[group]]:[@group],[@group])=1,XLOOKUP([@group],Ref[group],Ref[delta from budget]),"")

 

[AliGW]

Any good??? Some feedback would be nice ...