Hi I'm trying to get a formula that will lookup a list of items from Sheet1 A2:A an find in Sheet2 column G the first instance from any of the items and return the value from Sheet2 column A from the matching row. I've tried this formula and many more but was unable to get it working with index and match or with vlookup but nothing seem to work any help would be greatly appreciated.
=INDEX('Sheet2'!A:A, MATCH(1, INDEX(('Sheet2'!G:G = Sheet1!$A$2:$A), 0, 1), 0))
Use BYROW() with XLOOKUP().
=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,XLOOKUP(x,Sheet2!G:G,Sheet2!A:A)))
With traditional array formula-
=ARRAYFORMULA(IFERROR(XLOOKUP(A2:A,Sheet2!G:G,Sheet2!A:A),""))