How to utilize an INDEX MATCH function with a wild card to perform an appropriate match

[O_ch]

Would you know what is the correct match function with wild card. I have three sheets in one work book. They all do not have the same product description, all three sheets have the same header columns. I tried to use sheet1 and sheet two for the cell match Total (Qty) from both into Sheet3. Using sheet 1 & 2 have the product description and Total Qty. So Sheet3 needs from both only the Total Quantity. The issue is that Sheet 1 & 2 have no matching product description with Sheet "Total Quantity" Product description. Therefore it will return the corresponding Total Quantity result from both column C for the approximate match. Images have been uploaded. What I would like to see is Sheet "Total Quantity" Missing with Company (A) and Company (B) Total Quantity Sheet in work book. When I use my XLOOKUP function I do get the "QTY" numbers and they do not capture the correct column QTY from Sheet1 and Sheet2.

For example, if c2 is "abc" and my data is:

Column A to Column C
abc description 1
def description 2
ghi description 3

The formula would:
Match "abc" approximately in column A
and "def" approximately in column b
and "ghi" approximately in column c


Sheet1 Company (A)

Company (A)	Num	Product/Service	Memo	Item Description	Column1	(Company A) Qty	Sales Price
A	45843	46-1x36	1/2" X 3 YDS, ROLL	1/2" X 3 YDS, ROLL	1/2" X 3 YDS	4	$14.50
A	45625	Roll-1x36	1" x 36yd. Rolls	1" x 36yd. Rolls	1" x 36yd	10	$12.75

Sheet 1 of 1 Company (A)

Company (A)	Date	Transaction Type	Num	Product/Service	Item Description	Item Description1	Item Description2	(Company A) Qty
A	05/11/2022	Invoice	45843​	46-1x36	1/2" X 3 YDS, ROLL	1/2" X 3 YDS	1/2" X	$4.00​
A	01/05/2022	Invoice	45625​	HCM-RED-1x36	1" x 36yd. Rolls	1" x 36yd	1" x	$10.00​

Sheet2 Company (B)

Company (B)	Type	Date	Num	Memo	Name	Item	Item Description	Column1	Walker Qty2	U/M	Sales Price
B	Invoice	3/10/2022​	391737	Straight Strips	CF149	1522 Clear - 1" X 3" Strips, 36pc/Bag	1522 Clear - 1" X 3" Strips, 36pc/Bag	1/2" X	5​	ea	1.26​
B	Invoice	2/14/2023​	408352	Straight Strips	CF149	1523 Clear - 1" X 3" Strips, 36pc/Bag	1522 Clear - 1" X 3" Strips, 36pc/Bag	1/2" X	50​	ea	1.32​

Sheet2 of 2 Company (B)

Company (B)	Type	Date	Num	Memo	Name	Item	Item Description	Column1	Walker Qty2
B	Invoice	44630​	391737	Straight Strips	CF149	1522 Clear - 1" X 3" Strips, 36pc/Bag	1522 Clear - 1" X 3" Strips, 36pc/Bag	1/2" X	5​
B	Invoice	44971​	408352	Straight Strips	CF150	1522 Clear - 1" X 3" Strips, 36pc/Bag	1522 Clear - 1" X 3" Strips, 36pc/Bag	1/2" X	50​

Sheet "Total Quantity" Missing Company (A) and Company (B) Total Quantity

(Company A) Product	Item Description (Company A)	(Company B) Product	Item Description (Company B)	(Company A) Qty	Price (Company A)	(Company B) Qty	Price (Company B)
1522	1/2” x 3 yards	Tape	1/2" X 3 YDS, ROLL		$1.10		$1.17
1522	3/4” x 36 yards	Tape	3/4" X 36 YDS, ROLL		$9.95		$10.44

[ My Excel Function]
=IFERROR(XLOOKUP("Column1",Sheet1!A:M,9,FALSE),IFERROR(XLOOKUP("Column1",Sheet2!A:K,8,FALSE),Sheet2!I2)

So in total I have 5 Sheets and I would only like to use Sheet1 and Sheet2 to return the Quantity within my last work book "Sheet Total Qty".

 

[Afro_Cookie]

I don't think it will work with the current set up of your tables. It would be a lot easier if the descriptions matched or if there was some specific link between them that could easily be referenced.

If you modified the descriptions, a SUMIFS() formula might be able to get the qty totals you're looking for.