Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula help

Copper Contributor

Hi community

 

struggling to get my head round this...

I have 2 worksheets and need a formula for the following:

 

If column A (Customer) on Sheet 1 matches Customer on Column A on Sheet 2 then copy information from Column H Sheet 2 into Column B Sheet 1

 

Hope someone can help

 

Thanks

 

 

3 Replies

@GrandmasterMak 

=INDEX('Sheet 2'!$H$1:$H$1000,MATCH('Sheet 1'!A1,'Sheet 2'!$A$1:$A$1000,0))

=VLOOKUP(A1,'Sheet 2'!$A$1:$H$1000,8,FALSE)

 

You can enter one of these formulas in cell B1 of Sheet 1 and fill the formula down. With recent versions of Excel you can apply XLOOKUP alternatively.

Look up values with VLOOKUP, INDEX, or MATCH - Microsoft Support

@OliverScheurich thanks for the formulas but still struggling as i get a REF in the B1 Sheet 1 cell?

 

Forgot to mention i have headings in on both sheets:

SHEET 1 

GrandmasterMak_0-1703669228578.png

 

SHEET 2:

GrandmasterMak_1-1703669294679.png

 

So, want to match 'Customer' in Sheet 1 with 'Customer' in Sheet 2 and if a match copy the data in Sheet 2 'Client Release' (G) to Sheet 1 'Client Release' 'B' 

 

Hope you can advise further

 

Thanks

 

@GrandmasterMak 

In cell B2 of Sheet 1:

=INDEX('Sheet 2'!$G$2:$G$1000,MATCH('Sheet 1'!A2,'Sheet 2'!$A$2:$A$1000,0))

or

=VLOOKUP('Sheet 1'!A2,'Sheet 2'!$A$2:$G$1000,7,0)

 

If the data in the sheets starts in row 2 and if you want to return the results from column G of Sheet 2 you can apply one of the above formulas. The above formulas work if you don't have dynamic tables.

 

From your screenshots i can't tell if you work with dynamic tables. However if you use dynamic table you have to apply a formula like this

 

=INDEX(Tabelle3[client release],MATCH([@customer],Tabelle3[customer],0))

 

where you must replace Tabelle3 with the name of your dynamic table in Sheet 2.