Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel lookupif statement

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Lookup/IF statement

Excel Lookup/IF statement

ResolvedVersion 2010

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...

array function.xlsx

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

Attached files...

array function from if.xlsx

Excel tip:

Viewing two Excel 2010 Workbooks at the same time

Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!

1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.