LOOKUP formulas - if condition is not met then, check value in a different column

[lesfell]

Hello! Attached is my file for the reference of anyone willing to help me. I tried IF functions and vlookup but I can't quite figure out the formula I need.

In the GA tab in the attached file, under Actual column (D8) in LD, look for values of Basic LD (EH) in Mean tab, if value of Basic LD (EH) is 1, and the values of Intermediate (EJ), Advance (EL) and Superior (EN) LD is less than 1, then the return value should be 1, Otherwise, the return value is 0. But if any of the other levels have a value of 1, then the highest level reflecting 1 should be the basis of the returning value. ( Returning values: Basic = 1, Intermediate = 2, Advance = 3, Superior = 4).

Appreciate anyone that can help me. Thanks!!

 

[yogeshjoshi2020]

I have tried to solve your first half of query " In the GA tab in the attached file, under Actual column (D8) in LD, look for values of Basic LD (EH) in Mean tab, if value of Basic LD (EH) is 1, and the values of Intermediate (EJ), Advance (EL) and Superior (EN) LD is less than 1, then the return value should be 1, Otherwise, the return value is 0." which is highlighted in yellow as attached ..

2nd half query "But if any of the other levels have a value of 1, then the highest level reflecting 1 should be the basis of the returning value. ( Returning values: Basic = 1, Intermediate = 2, Advance = 3, Superior = 4)."
I am unable to understand. Will you please explain little more so that I can try to solve your problem.

 

[p45cal]

In D3, test this:

=MAX(0,IF(INDEX(Mean!$EH$1:$EN$99,MATCH($A3,Mean!$EE$1:$EE$99,0),{1,3,5,7})>=1,{1,2,3,4}))

Depending on your version of Excel, with older versions, you may need to commit this formula to the sheet with the key combination Ctrl+Shift+Enter rather than the more usual plain Enter. Then copy down.

 

[lesfell]

I have tried to solve your first half of query " In the GA tab in the attached file, under Actual column (D8) in LD, look for values of Basic LD (EH) in Mean tab, if value of Basic LD (EH) is 1, and the values of Intermediate (EJ), Advance (EL) and Superior (EN) LD is less than 1, then the return value should be 1, Otherwise, the return value is 0." which is highlighted in yellow as attached ..

2nd half query "But if any of the other levels have a value of 1, then the highest level reflecting 1 should be the basis of the returning value. ( Returning values: Basic = 1, Intermediate = 2, Advance = 3, Superior = 4)."
I am unable to understand. Will you please explain little more so that I can try to solve your problem.

Hello, yogeshjoshi2020.

To further explain the 2nd half, for example, in GA tab, if Name 6 (B6) has equivalent values of 1 in Basic, 1 in Intermediate and 1 in Advance, then the returning value should be 3 since Advance is equivalent to 1. Basic and Intermediate will no longer be considered.

Thank you so much for your help I appreciate it!

 

[lesfell]

In D3, test this:

=MAX(0,IF(INDEX(Mean!$EH$1:$EN$99,MATCH(A3,Mean!$EE$1:$EE$99,0),{1,3,5,7})>=1,{1,2,3,4}))

Depending on your version of Excel, with older versions, you may need to commit this formula to the sheet with the key combination Ctrl+Shift+Enter rather than the more usual plain Enter. Then copy down.

Thank you p45cal. I tried this one but I can't make it work

 

[p45cal]

There it is, in cell D3 and a few cells below in the attached.

 

[lesfell]

There it is, in cell D3 and a few cells below in the attached.

I tried using the formula in the workbook I have but it prompts me to save the workbook when I do CSE I don't know what else I should do, I am also supposed to use the same formula in the succeeding columns. I really appreciate your help, I'm just sad I can't get it to work on my end.

 

[p45cal]

1. What version of Excel are you using?
2. When you enter a formula which includes references to other sheets like this one does in Mean!$EH$1:$EN$99 and Mean!$EE$1:$EE$99 and that sheet (Mean) doesn't exist in the workbook you're adding the formula to it will try and get a reference from you to find the reference and amend the formula. You can escape from picking a file, which will allow you to edit the formula.
So what you need to do is examine what the formula refers to in the sample workbook we've been looking at, then adjust the formula to look at the equivalent sheets & ranges in your workbook.

 

[lesfell]

1. What version of Excel are you using?
2. When you enter a formula which includes references to other sheets like this one does in Mean!$EH$1:$EN$99 and Mean!$EE$1:$EE$99 and that sheet (Mean) doesn't exist in the workbook you're adding the formula to it will try and get a reference from you to find the reference and amend the formula. You can escape from picking a file, which will allow you to edit the formula.
So what you need to do is examine what the formula refers to in the sample workbook we've been looking at, then adjust the formula to look at the equivalent sheets & ranges in your workbook.

I am using Microsoft® Excel® for Microsoft 365 MSO. And yes, I changed the references as well, maybe my brain is fried already from the day. I'll try again when I wake up tomorrow. Thank you so much for your patience in helping me. I'll update you tom.

 

[p45cal]

You shouldn't need to CSE enter these formulae with the version you have.
A hint:
Mean!$EH$1:$EN$99 is a range of 7 contiguous columns, but you are only interested in alternate columns among them, viz. the 1st, 3rd, 5th and 7th. These should explain the {1,3,5,7} part of the formula.
The {1,2,3,4} part is the results you want if the value in those 4 columns is >=1.

Step through the formula calculation with:


to see what's going on.

 

[lesfell]

You shouldn't need to CSE enter these formulae with the version you have.
A hint:
Mean!$EH$1:$EN$99 is a range of 7 contiguous columns, but you are only interested in alternate columns among them, viz. the 1st, 3rd, 5th and 7th. These should explain the {1,3,5,7} part of the formula.
The {1,2,3,4} part is the results you want if the value in those 4 columns is >=1.

Step through the formula calculation with:
View attachment 78559

to see what's going on.

Thank you, I do understand your construction of the formula, but I still can't get it to work on my end even after I changed the references. It always returns an #N/A. I have attached the file I am working on for your reference.

 

[p45cal]

=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBNLA_Mean!$JX$1:$JX$99,0),{1,3,5,7})>=1,{1,2,3,4}))
becomes:
=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBLNA_Mean!$JU$1:$JU$99,0),{1,3,5,7})>=1,{1,2,3,4}))
(These are on row 8)

 

[lesfell]

=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBNLA_Mean!$JX$1:$JX$99,0),{1,3,5,7})>=1,{1,2,3,4}))
becomes:
=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBLNA_Mean!$JU$1:$JU$99,0),{1,3,5,7})>=1,{1,2,3,4}))
(These are on row 8)

Oh god, thank you I didn't catch that at all!! What if I want to use these same formulas to the succeeding columns do I just edit the range manually based on what I need from the CBLNA_Mean tab? You are a life saver thanks!!

 

[p45cal]

if I want to use these same formulas to the succeeding columns do I just edit the range manually based on what I need from the CBLNA_Mean tab?

Yes, you'll only have to change the blue bold section of the formula below:
=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBLNA_Mean!$JU$1:$JU$99,0),{1,3,5,7})>=1,{1,2,3,4}))
It's important that the CBLNA_Mean!$JX$1:$KD$99 and the CBLNA_Mean!$JU$1:$JU$99 ranges both start at the same row.

Separately…
Since you're using O365, you might be able to avail yourself of the new functions available.
I've done this in the attached see cell Q3 of the GA sheet where the formula is:
=LevelReached(CBLNA_Mean!$JW$2:$KD$99,CBLNA_Mean!$JU$2:$JU$99,$A3)
This is NOT a user defined function as in macros/vba but a LAMBDA formula in the defined Names of the workbook. This formula does NOT require the file to be macro-enabled - it can be a plain .xlsx file.
This reduces the formula to just 3 arguments with hints as you edit the formula; here's a screen shot of the formula being edited:


Here you see the first argument being edited (arrow) and the hint is showing ColmBlock in bold.
There is a slight difference in this formula; to try and make it a bit more intuitive, I noted that your data is in blocks of 8 columns so I get the formula to look at a whole block instead of starting one column in (you can see this formula starts at column JW, not JX, and includes 8 columns. So instead of looking at columns 1,3,5,7, it now looks at columns 2,4,6,8.
Again, it's important the ColmBlock and EDList start at the same row. You'll also notice that in this formula I started at row 2 for both ColmBlock and EDList; this is excluding the headers.
I did that because I also note that you have some Excel Tables in the CBLNA_Mean sheet, so take a look at cell R3's formula which uses that table's notation:
=LevelReached(Table66[[Mean of Basic Element 1]:[% of Superior Element 1]],CBLNA_Mean!$JU$2:$JU$66,$A3)
That formula looks at the data body of that table, hence EDList starts at row 2 (JU2).
If you'd extended that table to include column JU, the formula could have been:
=LevelReached(Table66[[Mean of Basic Element 1]:[% of Superior Element 1]],Table66[ED],$A3)

 

[lesfell]

Yes, you'll only have to change the blue bold section of the formula below:
=MAX(0,IF(INDEX(CBLNA_Mean!$JX$1:$KD$99,MATCH($A8,CBLNA_Mean!$JU$1:$JU$99,0),{1,3,5,7})>=1,{1,2,3,4}))
It's important that the CBLNA_Mean!$JX$1:$KD$99 and the CBLNA_Mean!$JU$1:$JU$99 ranges both start at the same row.

Separately…
Since you're using O365, you might be able to avail yourself of the new functions available.
I've done this in the attached see cell Q3 of the GA sheet where the formula is:
=LevelReached(CBLNA_Mean!$JW$2:$KD$99,CBLNA_Mean!$JU$2:$JU$99,$A3)
This is NOT a user defined function as in macros/vba but a LAMBDA formula in the defined Names of the workbook. This formula does NOT require the file to be macro-enabled - it can be a plain .xlsx file.
This reduces the formula to just 3 arguments with hints as you edit the formula; here's a screen shot of the formula being edited:
View attachment 78568

Here you see the first argument being edited (arrow) and the hint is showing ColmBlock in bold.
There is a slight difference in this formula; to try and make it a bit more intuitive, I noted that your data is in blocks of 8 columns so I get the formula to look at a whole block instead of starting one column in (you can see this formula starts at column JW, not JX, and includes 8 columns. So instead of looking at columns 1,3,5,7, it now looks at columns 2,4,6,8.
Again, it's important the ColmBlock and EDList start at the same row. You'll also notice that in this formula I started at row 2 for both ColmBlock and EDList; this is excluding the headers.
I did that because I also note that you have some Excel Tables in the CBLNA_Mean sheet, so take a look at cell R3's formula which uses that table's notation:
=LevelReached(Table66[[Mean of Basic Element 1]:[% of Superior Element 1]],CBLNA_Mean!$JU$2:$JU$66,$A3)
That formula looks at the data body of that table, hence EDList starts at row 2 (JU2).
If you'd extended that table to include column JU, the formula could have been:
=LevelReached(Table66[[Mean of Basic Element 1]:[% of Superior Element 1]],Table66[ED],$A3)

Thank you so much! I'll go explore this one during the weekend and see how it will work. For now I will just use the initial formula you provided Thanks again and have a great day ahead!

 

[Peter Bartholomew]

In my opinion references like
CBLNA_Mean!$JW$2:$KD$99
are absolutely revolting to work with. It tells you that the reference is remote (i.e. on another sheet) and in the middle of nowhere (column KD) but why should I care? The location tells me nothing about the content. Pascal has made some great first steps by introducing a Table and a Lambda function but more can be done to make sense of the data, with its alternating pattern of columns and multiple blocks (elements).

The first step could be to determine the indices required to access a single cell within the grid and create a Lambda function to return the value using those indices. For example

ElementPercentλ

= LAMBDA(EmpIdx,LevelIdx,Element,
      INDEX(DataTbl, EmpIdx, 2*LevelIdx+(1+8*(Element-1)))
  )

This allows a standard array notation to be used to access the data, e.g.
= ElementPercentλ(XMATCH(@ED,EDlist),levels,2)
where the italic part is chosen by the order in which one wishes to access rows and could be replaced by a simple index list.

I would suggest using MAKEARRAY to iterate over both 'employees' and 'elements' to return a single dynamic array of results.
= MAKEARRAY(64,4, LevelReachedλ)
where LevelReachedλ is

= LAMBDA(empIdx,elementIdx,
      XLOOKUP(1, ElementPercentλ(empIdx, levels,elementIdx), levels, 0, 0,-1)
  )