I have a formula It appears to work in the builder, and when I reference other formula in it, all appears ok.. but when in the field nothing shows up..I removed the IFFERROR trap while testing it.. any idea what I missed


=INDEX(Car_Shop!$D$2:$D$5797,MATCH($M$4&G2,Car_Sho p!$C$2:$C$5797&Car_Shop!$B$2:$B$5797,0),Car_Shop!$ D:$D,COUNTIF($G$6:$G$112,Car_Shop!$D$2:$D$5797))


F6 is the field of the first variable M4 another variable..noting 'status'
it compares f6 'job category' g6 is result to show the open unassigned employee id for each category.



the second sheet is the assignment Car_Shop sheet
column b column c column d

Category Status employee id
cat 1 U 123456
cat 2 U 134598
cat 3 A 123457


cat 4 U 123456
cat 5 A 123456
cat 1 A 659874
cat 2 A 458562


the result should [and is in the builder] be the next unique empl id in g6 based on
Car_Shop b and c column..any help would be immensely appreciated...

Did you array-enter the formula?

do you mean select the target columns in the builder..yes.. I get the, or what i see as a result, in the fx box..but nothing in the field..not even an error.. checked the formats of the field just to make sure something henky wasn't going on their.. selecting blk txt and wht background..changing fields.. nothing


and if you're seeing the spaces in the page references.. the site did that, I have opened and reopened it to 'fix' it and it is correct in that mode..

Debaser means did you commit the formula to the sheet using the key combination of Ctrl+Shift+Enter insead of just plain Enter?

hmmm.. yea

I have to set one up working with secure data

I'm trying to reproduce your set up here and can't work out what you're trying to do.
Best attach a minimal workbook with a few sample results.

Have we been rude?

Sorry I'm a lil off.. some of these turds never stop piling poo on me and this has been 4 days trying to figure why its not workin' and it's been awhile since I've had to do this stuff..

Attached Files

sample_sheet.xlsx (12.2 KB, 5 views)

No..no no one's been rude... honestly in the past when I hit up a forum.. I look n look n look..not finding something to work with, I'll post. To get those armchair "supreme masters" of whatever.. To beat ya up over a little something forgot... I used to do this stuff blindfolded lol but time and positions move up and I just don't have to deal with it that much.. So they run Ya through a ringer..but a post from some one else is just ""Ohh here is your mistake.. hope it helps".. So I stay far away from forums as a general means of keeping my sanity..


I was at it most of my free time after work monday and over the weekend.. my only 'day off' and one off my dogs decided to wake me up at 530 this morning..

I do appreciate the help.. and I'll reply later this afternoon... I'm going to try n get some sleep in..

I haven't got your complete answer yet but have put some formulae in the attached to return the first match, and since you have upper and lower case Statuses it look as though you might want to differentiate between them. There's a 3rd formula (headed 'more robust') which would differentiate if you had a Job ABC and a status D, and another job AB with status CD; both would use concatenations to ABCD, but column I would concatenate to ABC¬D or AB¬CD, which are different. This may not apply at all maybe because all your jobs are 3 letters and all your statuses are single letters.


What I'm still struggling with is the COUNTIF part of your formula.
Could you attach the file again but put in manually the results you're expecting from the data that's in your sample file?
Also whether you want this to be case-sensitive or not.

Attached Files

msofficeforums49335sample_sheet.xlsx (13.9 KB, 8 views)

Just had a thought while away from the computer; are you looking for the formula to give you both columns, as in the likes of for Status S:
2022-07-12_205028.png
and for Status A:
2022-07-12_205048.png?

The topmost picture in the 11:52 post is what I was intending, well they both work..You made my year!.. The countif function is what I found in a reference when I revisited the task, as a 'requirement' for it to filter the column..



Apologies for not getting back on yesterday.. Got 2 hrs in and at it again..

It doesn't have to differentiate between the status format or in my case lack of.. It is intended to just return a unique id result [D] based on [B] and [C]. So one Available ID is shown for the returned category and the next result; though it is same category returns the next available ID..

Thank You for the help, I still can't get it to show a result though the builder says there is one. stepped away from it clear the brain to muddle with vb objects.. there is a program that it is needed to export to.. probably the reason the workbook became a lil jenky.. someone didn't know that saving a wb as csv was a no no at some point without saving prior.. I'll add something that will do it blind without a lot of fuss.

Ironically I was grumbling about it went back in tues and the super'v laughed and said "I asked ya to make that sometime in '04 long before ya came on board".. my reply was well I'm going to Tech and finding out why we aren't using sql for all this hooey...

Attached Files

sample_sheet.xlsx (12.3 KB, 6 views)

Hmmmm that might actually be useful.. right now all I added was a count for status' that aren't A/vailable.. Your going to get me in a rabbit hole with this lol. Back in the day when did this I would wrap one thing up and like you realize a new function. Back then I did a lot of freebie work for friends of friends and whoever hoping to land a backend tech position..

I'm not seeing a result in the 'builder':
2022-07-13_124614.png
It's also complaining of a circular reference.


OK, the file has slightly different data in it, so asking msg#11 again:
Is this what you want to see for A?:
2022-07-13_124857A.png
and this for S?:
2022-07-13_124828S.png


Edit post posting. I see you answered msg#11 while I was responding. So you might find that useful.

Could you post a file with you having filled in manually the results you expect to see?

Maybe it's this for A:
2022-07-13_130151A.png
and this for S:
2022-07-13_130215S.png
?