Forum Discussion
Excel formula
You may add named formulas for each cell with flag, e.g. for L17
=XLOOKUP('Linked Picture'!$L$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)and for O17
=XLOOKUP('Linked Picture'!$O$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)
Attaching the file (remove any confidential/personal info) would help a lot.
But if the equation:
=INDEX('Linked Picture'!$AA$31:$AA$46, MATCH('Linked Picture'!$G$16,'Linked Picture'!$Z$31:$Z$46,0))
worked and now you just want to show blank if G16 is blank then
=IF(G16="","",INDEX('Linked Picture'!$AA$31:$AA$46, MATCH('Linked Picture'!$G$16,'Linked Picture'!$Z$31:$Z$46,0)))
should do it.
mtarler Hi.
Thanks again. I tried that but got no joy.
I'm guessing it may not be possible.
At any rate the file is attached.
Thanks for your time. Dennis
As named formula you need to use any function which returns reference, that could be OFFSET, INDEX or XLOOKUP.
If XLOOKUP is available for you Excel, you may handle empty cells with it. First, add dummy picture at the end of your range with flags (in general it could be in any other place), like
I take blue to make it visible, after testing it could be change on any transparent shape of any form.
Next, add named formula as
=XLOOKUP('Linked Picture'!$O$28, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)Last parameters indicates what to return if nothing is found. I named it GetFlag. If country exists it gives
If empty cell with country name
Please check in attached file.
- Thank you Sergei. I will try - I am not very advanced with this stuff! I have looked a bit but don't see how it can work as the Getflag is different from my FlagLookups (1 for each team). I was hoping I could do something like this:
input results in R6 and S6
gives me a value in L16 then
in L17 something like
=XLOOKUP(L16,Z31:Z46,AA31:AA46,"",0,)
I know this can lookup the value of L16 and check it against Z31:Z46 but then I want it to insert the matching flag to L16 or return nothing if L16 is empty. I know I can not just stick in AA31:AA46 and the rest as I have done here in my example. Anyway, I will keep thinking about it. Very many thanks for your help. DennisYou may add named formulas for each cell with flag, e.g. for L17
=XLOOKUP('Linked Picture'!$L$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)and for O17
=XLOOKUP('Linked Picture'!$O$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)