asked on

Function to retrieve value not present

I'm looking for a quick formula to simply display the value that is NOT in a list.
My row will contain
1 2 v 3 4

My source is basically the numeric range 1-5 (I do have that in a column if it needs to be referenced).

I would simply want to have the formula spit out '5' against that row.
What function can I use?

Bill Prew

Can you provide a small XLS file as an example, and the results you want?

It's not clear to be what values are in cells versus rows and columns, etc.

»bp

sirbounty

ASKER

https://docs.google.com/spreadsheets/d/1esCkvQlVV_ReIHT0umR936uMmuRaAKrjcaCrQffqZkw/edit?usp=sharing

Bill Prew

Are you looking for an Excel solutions, or Google Sheets?

If Excel I would probably do a small VBA macro function. I'm not thinking of any "simple" approach using a formula that uses only built-in native functions, although with a lot of gymnastics it might be possible.

If Google Sheets then I'll defer to other experts, I don't work with that...

Also, what if multiple values from the "range" are not present, how do you want them to show up in the result? Comma delimited, or something else...?

»bp

sirbounty

ASKER

Sorry - google sheets.
I've seen quite a bit of gymnastics that might work - but outside of my expertise as well.
Simple sheet - not going to be terribly complex. It's a simple tournament format where I'm trying to find the work team - the one not competing in that round...

Bill Prew

I added "Google Apps" topic to this question, maybe a Google Sheets guru will notice it.

»bp

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

Easier than expected - thanks! :)