In Column A, I have a unique key. Column L is where I need the formula.
I'd like the formula to find the unique key in A2 in Tab2 of the spreadsheet in Column D - then return the FIRST non - blank value in Tab2 Column AA. I know it's an index match. =INDEX(Tab2!AA:AA,MATCH(A2,Tab2!D:D,0)) but can't figure out where to put the ISBLANK
Thanks,
Hi,
This doesn't make sense. The Match will return whichever row contains the A2 value in column D. So mention of the first non blank in column AA is irrelevant since with the Index function you'll find the specific row number identified by the Match
Maybe you need to upload the workbook and manually add some typical results so that we can see what you mean. Maybe you want to find the first non blank cell in column AA AFTER the Match row, but I'm guessing here.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sheet 1 shows what i want the final value to be. Column L is where the formula would be, matching back to sheet2 to find the same ID18 and returning the campaign that is not blank.
Thanks
This array formula** entered in L2 and copied down:
=INDEX(Sheet2!AA:AA,SMALL(IF((Sheet2!D$2:D$12=A2)*(Sheet2!AA$2:AA$12<>""),ROW(Sheet2!AA$2:AA$12)),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
..or another way using just a Pivot Table
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks