Return if a match and details of match

[SallyM]

Hi I'm hoping someone can help me or tell me it's just not possible!

I'm trying to create an automatic stock checker by entering a list from our system versus external location reports, once I've entered the data I can only seem to show if there is a match but not which item, status or where the item is located

I can get it to return a yes or no, 0 or 1, true or false but nothing that can show item number, location and status e.g. ITEM1234561 Savannah AVLB (On the system list as AVBL and on the Savannah USA Savannah list) and if no match to show ITEM1234561 Savannah No Match (Not on the system list but on the Savannah list) or ITEM1234561 No Match AVLB (On the system AVLB list but not on any location)

I've tried using XMATCH, XLOOKUP, MATCH, VLOOKUP, IFS, INDEX etc and not getting very far

First I added a data input sheet where everything is entered, then there is a second sheet which converts all to the same format (using =SUBSTITUTE('Data Input'!F5,"/","")), third sheet has the converted items and a status or location to the right (I've used some formulas before where I could put if a match return left or right cell so my logic was to try and use that somehow) and a fourth to show the overall results (trying to us =INDEX(Formula!$B$5:$T$19,MOD(ROW()-ROW($B$3)-1,ROWS(Formula!$B$5:$T$19))+1,INT((ROW()-ROW($B$3)-1)/ROWS(Formula!$B$5:$T$19))+1) but it's coming up with #SPILL! to list all tanks mentioned on the second sheet)

Not sure if that makes any sense - I've uploaded some screenshots as my system won't allow the extension download...

Cheers!

 

[OaklandJim]

It's difficult -- for me at least -- to tell what you need and what the data looks like. The pictures are not readable.

This might be a job for VBA but, like I said, I cannot tell. Plus I'm not that great with complex formulas.

Might you provide a workbook (via a link) to look at? If so maybe include some annotations (I use text box shapes) so we can see your data.

The XL2BB addin is really good. Maybe do it from home if providing a workbook is not practical.

If it is helpful, that #SPILL! thing means that your formula returns an "array" of values that overlaps with cells containing data.

 

[SallyM]

Thanks for trying! Hopefully this link with work: Test Stock Report SM - Mr Excel.xlsx

I think I'm over complicating it but can't think of a way to get the results I need without manually checking each list...

Appreciate your help!

 

[OaklandJim]

I may be out of town without internet for a while but maybe someone can assist.

Based on the workbook provided it is still difficult for me to understand the need. (Other smarter ones on the list MAY understand.)

It might be good to use VBA but I cannot tell. For example a macro could be set up to iterate through data looking for the desired result and put the results into a Results worksheet.

In cells B3 to D3 in the Results worksheet what exactly is being sought and from where? Maybe a few example results done by hand would help.

Consider using Insert => Comment to put an explanation into cells with any cell-specific notes, which might help.

 

[Fluff]

Hi & welcome to MrExcel.
This will only work in a normal range, not a table, but how about

Test Stock Report SM - Mr Excel.xlsx
	A	B	C	D
1
2		Item	Where	Status
3		ITEM1234561	Savannah	AVLB
4		ITEM2345671	Cincinnati	Pre-Order
5		ITEM3456781	Jacksonville	Returns
6		ITEM4567891	Mobile	Repairs
7		ITEM3456785	Mobile	Returns
8		ITEM1234565	Jacksonville	AVLB
9		ITEM1234567	Chicago	AVLB
10		ITEM1234566	Louisville	AVLB
11		ITEM1234568	New Orleans	AVLB
12		ITEM1234562	Detroit	AVLB
13		ITEM1234569	St Louis	AVLB
14		ITEM1234563	Newark	AVLB
15		ITEM2345672	Charleston	Pre-Order
16		ITEM1234564	New Jersey	AVLB
17		ITEM2345673	Houston	Pre-Order
18		ITEM2345674	Norfolk	Pre-Order
19		ITEM2345679	Pasadena/Best	Pre-Order
20		ITEM3456782	Louisville	Returns
21		ITEM4567892	Detroit	Repairs
22		ITEM8912341	Savannah
23		ITEM3456784	Chicago	Returns
24		ITEM3456786	New Orleans	Returns
25		ITEM2345676	St Louis	Pre-Order
26		ITEM3456787	Newark	Returns
27		ITEM3456783	Cincinnati	Returns
28		ITEM2345677	Charleston	Pre-Order
29		ITEM9876541	New Jersey
30		ITEM2345678	Houston	Pre-Order
31		ITEM3456789	Norfolk	Returns
32		ITEM4567893	Pasadena/Best	Repairs
33		ITEM4567898	Newark	Repairs
34		ITEM4567897	Charleston	Repairs
35		ITEM4567896	Houston	Repairs
36		ITEM4567895	Pasadena/Best	Repairs
37		ITEM4567894		Repairs
38		ITEM2345675		Pre-Order
39		ITEM3456788		Returns
40		ITEM4567899		Repairs
41
42
Results

Cell Formulas
Range	Formula
B3:B40	B3	=UNIQUE(SUBSTITUTE(TOCOL(Table2,1),"/",""))
C3:C40	C3	=LET(a,SUBSTITUTE(Table2[[Mobile]:[Pasadena/Best]],"/",""),b,Table2[[#Headers],[Mobile]:[Pasadena/Best]],r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),c,TOCOL(a,,1),d,INDEX(b,INT(s/r)+1),BYROW(B3#,LAMBDA(br,XLOOKUP(br,c,d,"",0))))
D3:D40	D3	=LET(a,SUBSTITUTE(Table2[[AVLB]:[Repairs]],"/",""),b,Table2[[#Headers],[AVLB]:[Repairs]],r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),c,TOCOL(a,,1),d,INDEX(b,INT(s/r)+1),BYROW(B3#,LAMBDA(br,XLOOKUP(br,c,d,"",0))))
Dynamic array formulas.

 

[SallyM]

Hi - thanks!

Sorry for not replying sooner - I have just tried this out and it's perfect!!

Thank you so much!!

 

[Fluff]

Glad to help & thanks for the feedback