Merger index

[parsec]

Here is my Issue.
Sheet1 (with four Columns )
ItemNumber | Name | QtyOnHand | Actualcount

Sheet2 (With two columns)
ItemNumber | Qty

I am using this formula "=INDEX(Sheet2!$B$2:$B$7,MATCH(A2,$A$2:$A$7,0))" to match the ItemNumber on any cell in both of the Sheets and get the value fro Sheet2 ""Oty" into Sheet1 Actualcount
Some how it has become a difficulty for me to make it work.
if possible I like to see how the INDEX/MATCH, or Power Query works
thank you
John

 

[sandy666]

if possible I like to see how the INDEX/MATCH, or Power Query works

post representative example of source data and expected result. Use XL2BB to do it

 

[parsec]

I have try to install the add in but no luck, I am using 365 please advise

 

[sandy666]

did you try to read Installation part from the link above?

 

[parsec]

test10 28 2020.xlsx
	A	B	C	D
1	ItemNumber	name	QtyOnHand	Actualcount
2	123	test123	50	111
3	321	test321	10	60
4				#N/A
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX(Sheet2!$B$2:$B$7,MATCH(A2,$A$2:$A$7,0))

test10 28 2020.xlsx
	A	B	C	D
1	upc	count
2	321	111
3	123	60
4
Sheet2

 

[sandy666]

maybe

Table1			Table2				Result
upc	count		ItemNumber	name	QtyOnHand		ItemNumber	name	QtyOnHand	Actualcount
321	111		123	test123	50		123	test123	50	60
123	60		321	test321	10		321	test321	10	111

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Join = Table.NestedJoin(Source2,{"ItemNumber"},Source1,{"upc"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"count"}, {"Actualcount"})
in
    Expand

 

[parsec]

did you try to read Installation part from the link above?

Thank you got it

Thank you

if possible can you please explain what I was doing wrong with my formula? I like to learn from that too

 

[sandy666]

Book1
	B	C	D	E	F	G	H
1	Table1			Table2
2	upc	count		ItemNumber	name	QtyOnHand	Actualcount
3	321	111		123	test123	50	60
4	123	60		321	test321	10	111
Sheet1

Cell Formulas
Range	Formula
H3:H4	H3	=INDEX($C$3:$C$4,MATCH(E3,$B$3:$B$4,0))

 

[parsec]

Thank you for your time.
I like to ask you another question.
I have connect via ODBC the above Sheet(table2) to an active database , I am able to get the data that I need from the database. What I like to do, is update the QtyOnHand (a field in the database) from the Actualcount.
I will apricate any a direction to the matter
Thank you

 

[sandy666]

sorry mate, I can't help
create new thread with this question