Lookup with Variables
This formula needs to be in Col AR
I have tried =LOOKUP(AND(F4,"??? Total"),F4:F720,AI4:AI720) I need to find a cell in Col F that contains the value from the same row followed by the word "Total" and returns the value from Col AI in the same row. (ie. F4 = 108, F7 = 108 Total, AI7 = 7.05) The problem is that I have multiple cells with "??? Total" where "???" is a three digit number relating to the group above it. Can anyone help? ~Robert |
Lookup with Variables
Try this:
=INDEX(AI4:AI720,MATCH(F4&" Total",F4:F720,0)) Biff "rlee1999" wrote in message ... This formula needs to be in Col AR I have tried =LOOKUP(AND(F4,"??? Total"),F4:F720,AI4:AI720) I need to find a cell in Col F that contains the value from the same row followed by the word "Total" and returns the value from Col AI in the same row. (ie. F4 = 108, F7 = 108 Total, AI7 = 7.05) The problem is that I have multiple cells with "??? Total" where "???" is a three digit number relating to the group above it. Can anyone help? ~Robert |
Lookup with Variables
Maybe something like this:
With your posted scenario and A1: 108 This formula returns the "108 Total" value (assuming that "108 Total" appears only once in the range) B1: =SUMIF(F4:F720,A1&" Total",AI4:AI720) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "rlee1999" wrote: This formula needs to be in Col AR I have tried =LOOKUP(AND(F4,"??? Total"),F4:F720,AI4:AI720) I need to find a cell in Col F that contains the value from the same row followed by the word "Total" and returns the value from Col AI in the same row. (ie. F4 = 108, F7 = 108 Total, AI7 = 7.05) The problem is that I have multiple cells with "??? Total" where "???" is a three digit number relating to the group above it. Can anyone help? ~Robert |
All times are GMT +1. The time now is 09:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com