admin管理员组

文章数量:1123098

Is there a lookup or similar function that I can use in excel to search for an exact match and then retrieve a cell 48 cells right and 1 below? I have attached an image of the automated report that is generated below.

I need to retrieve the total that is in the highlighted cell (column AY) by searching the UserID that is one row up and in column B.

I have tried =INDEX(JDA!B:B,MATCH(A4,JDA!B:B,0)+1,48) and XLOOKUP functions, but cannot seem to come up with anything good.

Is there a lookup or similar function that I can use in excel to search for an exact match and then retrieve a cell 48 cells right and 1 below? I have attached an image of the automated report that is generated below.

I need to retrieve the total that is in the highlighted cell (column AY) by searching the UserID that is one row up and in column B.

I have tried =INDEX(JDA!B:B,MATCH(A4,JDA!B:B,0)+1,48) and XLOOKUP functions, but cannot seem to come up with anything good.

  • excel
  • excel-formula
Share Improve this question edited 4 hours ago BigBen 50k7 gold badges27 silver badges44 bronze badges asked 5 hours ago Alex HagoodAlex Hagood 52 bronze badges 1
  • 2 =INDEX(JDA!AY:AY,MATCH(A4,JDA!B:B,0)+1,)? Btw OFFSET as suggested in an answer is volatile and its use can be avoided. – BigBen Commented 4 hours ago
Add a comment  | 

1 Answer 1

Reset to default 0

=offset( xlookup( A4, JDA!B:B, JDA!B:B,0,0),1,48)

You may have to change the lookup value and the search and return array but this will check column B for the value in A4, returning the exact match 1 row down and 48 rows to the right.

本文标签：

版权声明：本文标题：Is there a lookup or similar function that I can use in excel to search for an exact match and then retrieve a cell 48 cells rig 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736548716a1944484.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。