IF and VLOOK UP

[kkimiri]

Hi,

I am using and IF and VLOOK UP formula. However when a certain number of rows are exceeded the formula returns a nil.

Please see attached excel. In the summary Tab cell E 11 returns a blank when row no.11 is passed in details tab.

Please assist to have the formula return the values in the summary tab as per the values in the details tab.

Thanks

 

[Luke M]

This part of your formula is odd:
=IF($E$5=Details!L:L, ...

The IF function doesn't normally compare an entire column to a single cell. XL is interpreting this as compare E5 to the cell in col L at same row formula is in. So, in E11 of Summary sheet, it's looking at L11 on Details sheet (which is blank). This doesn't equal E5, so formula evaluates to "".

You'd need to explain more what the goal of summary sheet is for me to offer a potential solution. Otherwise, as is, you need to shift everything on the details sheet 1 row down (could insert a row above the table).

 

[kkimiri]

Thank You for the reply, appreciated.
On the summary tab, i have created the drop down button. What i intend to achieve is that when i select 5/NOR in the summary tab the formula returns the figures in the columns (people, unit, quantity) that match those in the details tab and are only under 5/NOR (i.e column L) and when i select 8/SWI in summary tab it returns the same figures from details tab that relate to 8/SWI in column L. (for example when i select 8/SWI in drop down button, potatoes row should bring 300 people and quantity of 500.
I hope am clear.

 

[Peter Bartholomew]

Your IF statements are not connected to the table. The value picked out from column K is the value "5/NOR" or "8/SWI" that just happens to be on the same row as the formula (implicit intersection). To get information that corresponds to the formula you need to look up the relevant information the same as for the other data.

It is also possible to return all the data with a single array formula but I don't think you should go there!

p.s. The workbook looks a little different but that's the way I work.

 

[bosco_yip]

Giving 2 options without formulas

1] Option 1

1.1] Delete row 6

1.2] Your formulas remain

2] Option 2

2.1] Copy/paste from "Details" sheet E6:J15 to D17:I26

2.2] Select E18:I26 >> Conditional Formatting >>

>> new rule formula : =Details!$L7<>$E$5

>> "Format" : font color choose white

Regards
Bosco

 

[kkimiri]

h

Your IF statements are not connected to the table. The value picked out from column K is the value "5/NOR" or "8/SWI" that just happens to be on the same row as the formula (implicit intersection). To get information that corresponds to the formula you need to look up the relevant information the same as for the other data.

It is also possible to return all the data with a single array formula but I don't think you should go there!

p.s. The workbook looks a little different but that's the way I work.

Hi Peter,

Thank You so Much. This works perfect. Very Much Appreciated.

And you are right, the single array formula is on another higher level for me.

P.S the workbook looks good and fine with me.

Thank You once again.

 

[kkimiri]

Giving 2 options without formulas

1] Option 1

1.1] Delete row 6

1.2) Your formula remain

2] Option 2

2.1] Copy/paste from "Details" sheet E6:J15 to D17:I26

2.2] Select E18:I26 >> Conditional Formatting >>

>> new rule formula : =Details!$L7<>$E$5

>> "Format" : font color choose white

Regards
Bosco

Thanks a lot Bosco. Option 1 works well for me.

Much Appreciated.