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 and welcome to the board.

If the date is always in the same column (A for example) and the lookup name is also always in the same column (for example C), you could use something like this:

=INDEX(A1:A10,MATCH("Ken Baker",C1:C10,0)-2,0)

 

[Holly92]

Thank you very much for your quick reply!


Whilst using the formula, I keep getting the dreaded '#N/A'

Does the -2,0 at the end mean the cell 2 rows to the left should be extracted? As I have adapted it as follows, but I'm not sure I have understood it correctly.
=INDEX($F$2:$F$500,MATCH(X10,$F$2:$F$500,0)-2,1)

Many thanks,

Holly

 

[Joyner]

The -2 is basically looking UP two rows. In my formula, INDEX in Column A, and the MATCH in column C, is what is getting the results over a number of columns (C to A)

INDEX in telling the formula to return a value from that column, your INDEX formula is using column F, is that where the date is that you want returned?

MATCH is the actual lookup part of the formula, and most likely the #N/A means it is not finding your lookup value from X10. Are you sure the lookup value in X10 is somewhere in column F? If so are they actually the same/identical values/text.

If you can post a sample of your data, we can figure it out.

 

[Joyner]

P.S. If you are familiar with VLOOKUP, it is worth it to take the time to figure out how INDEX and MATCH work, it will really expand what you can do with formulas...

 

[Holly92]

Ah, I have found that the problem is some of the cells in column F are merged! I have unmerged them all now.

Thank you for your explanation. I understand now that the formula works correctly for extracting the info from the cell two rows up. However, I require the formula to instead return the information from the cell 2 rows up and then 4 cells to the left. Is this possible with the INDEX/MATCH function?

Here is a sample to help better explain:

[TABLE="width: 585"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mrs Abbott[/TD]
[TD]Mr Watson[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD]Mrs Abbott[/TD]
[TD][/TD]
[TD]Last day of month[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Client Address[/TD]
[TD][/TD]
[TD]Surrey[/TD]
[TD][/TD]
[TD]Consecutive?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Booking Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD]Mr Watson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Client Address[/TD]
[TD][/TD]
[TD]Hampshire[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Booking Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]04/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]05/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]06/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Unfortunately, I am not able to change the format of the report (I am working with automated reports -trying to gather info quickly from numerous reports similar to the example above, but far more lengthy). The aim is to extract the last booking date for each client.

When I input the formula =INDEX(D3:D21,MATCH(H2,D3:D21,0)-2,-2) into cell F2 in this example, I would like it to find the next client name, count 2 up and 2 left, and extract the info in that cell (in this example, A11).

I clicked on 'show calculation steps' and found that the formula is correct up until the very final number.

For the next stage, I would like to be able to assess if the booking date range is consecutive. I think this could be done for each client by returning the cell number of the cell below 'booking date' and two above 'Client name'. Perhaps I am jumping ahead!

For either stage, extracting information from specific cells is key. So I would greatly appreciate any advice on how this can be achieved with the INDEX/MATCH formula, or whether a different combination (perhaps with Vlookup) is required.

Best wishes,

Holly

 

[Joyner]

Okay, so if I understand correctly, for Watson, the formula at G2 to extract 31/01/2017 would be:

=INDEX($A$1:$A$25,MATCH(G1,$C$1:$C$25,0)-2)

and do you want to see if the dates for Watson at A9 and A11 are consecutive? If so, this formula:

=INDEX($A$1:$A$25,MATCH(G1,$C$1:$C$25,0)-2)=INDEX($A$1:$A$25,MATCH(G1,$C$1:$C$25,0)-4)+1

For this to work, you have to know if the dates are actual date values in Excel, if not, we would need to address that by either using Text to Columns, or modify the formula.

This would return TRUE or FALSE, but we could make it say anything you want.

For Abbott, in your example the formula won't work because there is no value two cells up, but we can capture that with an IFERROR formula if you want.

 

[Holly92]

Thank you! Both formulas work well, thank you.

I've identified the cell address for the first and last bookable date for each client. As this then gives me the date range, could I use an IFERROR formula to count the number of unique dates within that range?

My aim is to see if each client had a full month booked. Where the consecutive formula identified a client as 'false', I would like to be able see how many days in the month are missing.

For Abbott where there is no value two cells up, I added in a random name underneath the data. But if we can make it automated instead by capturing it with an IFERROR formula, that would be great!

Thank you so much for your help here! I feel like I am learning a lot!

Best wishes,

Holly

 

[Joyner]

Hello Holly,

A couple things. I think I would need to see a more representative set of data to see what you want to do. When you say you want to count the number of unique dates and see how many days in the month are missing, you are more than likely talking about a fairly complex array formula. How much data do you actually have - how may rows?

Looking at what you have, and since you say you are working with automated reports and some are far more lengthy, the first thing I would ensure is that I can't get the data output into a proper data set. If not then what I have done in that case in automate cleaning the report and putting it into a proper data set that you and then more appropriately analyze in any way you want.

I have automating cleaning up far more difficult reports and that would be the way to got in my opinion.

If that is not an option we can see what we can do.

 

[Holly92]

Hello,

Thank you for your massive help with this!

Thank you for your suggestions but I have played around with it some more and have managed to ascertain the information I am looking for.

There is one thing left that I am struggling with, are you able to offer some advice, please? I would like to find the number of dates in a range, so I type in, for example, =B129-B73. These two cells are the first and last date entries in the date range. The result is 28 days (28 entries). I have written the addresses for these two cells in other cells. I would like to carry out this subtraction calculation using the cells which contain the cell addresses in the subtraction formula. I think an INDIRECT function would allow me to identify the contents of a cell as an address to use in a formula, rather than the value itself.

For example,

[TABLE="width: 410"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]cell address[/TD]
[TD]$B$129[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]cell address[/TD]
[TD]$B$73[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]=SUM(INDIRECT(B1&"-"&B4))

[/TD]
[/TR]
</tbody>[/TABLE]
I have used this SUM/INDIRECT function in B6 to try and get the formula to identify B1 and B6 as addresses rather than values. So B1 refers to cell B129 which contains 15/01/2017, and B73 contains 01/01/2017. I would like B6 to recognise this and return 15. My formula just returns #REF , and I think having dates in the original cells perhaps complicated this also. Are you able to offer any advice, please?

Many thanks,

Holly