Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course
Excel Lookup/IF statement
Hi
I have 4 cells in a row that contain If statements but essentially either produce FALSE or a text value i.e. TTF, or BBVP L, etc. In the 5th cell, I need to extract this text value from whatever one of the previous 4 cells it's in, and if there are no entries in these cells (because there is no data) then no value is required in the 5th cell.
Example of how it could look
1 2 3 4 DESIRED 5
FALSE FALSE FALSE TTF TTF
FALSE BBVP-L FALSE FALSE BBVP-L
FALSE FALSE EGTHUB FALSE EGTHUB
Simple solution is to choose whichever cell is not false, or alternative is 5 look up against a list of lets say TTF, BBVP-L, EGTHUB and whatever it matches across the row of 4 cells thats in this list it picks
Any help would be hugely appreciated, as I believe its the last bit of the puzzle
RE: Excel Lookup/IF statement
Hi Adrian,
Thank you for a nice challenging question.
I have used the IF, Index & Match function as array functions to make it work.
I have attached a workbook where you on sheet 1 can see the explanation and my example.
Please let me know if you have problems with my example.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: Excel Lookup/IF statement
Hi Jens
Thank you very much for your help - much appreciated. Silly question though, when can I find the attachment?
Kind regards
Adrian
RE: Excel Lookup/IF statement
nevermind just found it
RE: Excel Lookup/IF statement
Hi Jens
The problem is, that these cells that the Array looks at contain IF statements, so if I try to amend the if statement in any way and press enter or ctrl+shift+enter, then the formula is just displayed in the cell, instead of the result. Any suggestions?
RE: Excel Lookup/IF statement
Hi Adrian,
I have tested my functions from cells with if functions and for me it worked fine (see attachment). I cannot tell you why it is not working for you, but if you want me to have a look at your workbook you can send it to:
info@stl-training.co.uk
Please add me name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector