match if functions

[jbrown021286]

i have 2 worksheets 1 named data and 1 named report. if a cell in column C of data matches a cell in column A in report i need columns B,C,D,E,F,G, and h of reports to populate the corresponding rows in columns G,H,I,J,K,L, and M in data. what is the best way to achieve this

 

[Fluff]

How about

=XLOOKUP(C6,Report!$A$2:$A$100,Report!$B$2:$H$100,"no match")

 

[ExceLoki]

try this

=FILTER(report!$B$1:$H$30,report!$A$1:$A$30=C6,"no match")

 

[jbrown021286]

How about

=XLOOKUP(C6,Report!$A$2:$A$100,Report!$B$2:$H$100,"no match")

is there also a way to make this work with the "get data from table/range function. right now it works till i create a table then it does this

 

[Fluff]

You cannot use spill ranges in a table. The best thing is to convert the table to a normal range, it's far more efficient than using multiple formulae, when one will do.

 

[Alex Blakenburg]

If you are pulling the data into Power Query, you would normally do the lookup in Power Query using a Join in PQ called a Merge.
As Fluff alluded to you can do it in the table by doing a column by column lookup instead of relying on the formula spilling.

ie put this in column G and copy it across to H-M

=XLOOKUP($C6,Report!$A$2:$A$100,Report!B$2:B$100,"no match")

($C6 would normally be replaced with the table structured reference for Column C and the current row (@)