Extracting info from a cell 3 rows up and 2 left to a cell found on an IF function

[Holly92]

Please help! First time posting.

I am not very experienced in using Vlookup, and have been going round in circles trying to work this out.

I would like to search for a cell using an IF function, and then extract the information from the cell two rows up and three cells to the left.


Here is an example of my dataset:

[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]19/01/2017[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 128, colspan: 2"]Customer name[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl64, width: 512, colspan: 8"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67, width: 192, colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68, width: 128, colspan: 2"]Customer name[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD][/TD]
[TD="class: xl64, width: 512, colspan: 8"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67, width: 192, colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 128, colspan: 2"]Employer name[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 576, colspan: 9"]Ken Baker[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]01/02/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68, width: 128, colspan: 2"]Customer name[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD][/TD]
[TD="class: xl64, width: 512, colspan: 8"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67, width: 192, colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68, width: 128, colspan: 2"]Customer name[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD][/TD]
[TD="class: xl64, width: 512, colspan: 8"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67, width: 192, colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 128, colspan: 2"]Employer name[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 576, colspan: 9"]Malcolm Walker
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to be able to search for 'Ken Baker', for example, and then return the date that is located two rows up and three cells to the left (31/01/2017). I would then like to be able to do this for Malcolm Walker as well.

Any help with what the formula should look like, would be much appreciate!


Thanks, Holly

 

[Joyner]

Hello Holly, I'm not sure I exactly follow what you need to do, but for the INDIRECT formula you show, this should work to subtract the two cell addresses:

=INDIRECT(B1)-INDIRECT(B4)

 

[Holly92]

That's perfect, thank you!

Although if I nest it in a SUM FREQUENCY formula (below), it returns #REF .

=SUM(--(FREQUENCY(INDIRECT(B6):INDIRECT(B68),INDIRECT(B6):INDIRECT(B68)>0)))

Have I written this incorrectly?

Many thanks,

Holly

 

[Joyner]

What is in cells B6 and B68? is that the $B$129 and $B$73, or something like that you show above in cells B1 and B4? For the INDIRECT it is looking for a cell reference in B6 and B68 like you show above.

Are you trying to count the values between the range? if so this would do it:

=COUNT(INDIRECT(B6):INDIRECT(B68))

or if the range to count is actually B6 and B68 then:

=COUNT(B6:B68)

If you are trying to count the unique values in that range then try:

=SUM(IF(FREQUENCY(INDIRECT(B6):INDIRECT(B68),INDIRECT(B6):INDIRECT(B68))>0,1))

Likewise as above you can remove the INDIRECT if B6 to B68 in the actual range where the values are to count.

 

[Holly92]

Perfect!

Thank you very much

 

[Joyner]

you are welcome