• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match data that is not in a list and return the cell value below

A

aneelaj

New Member
Hi all,


I have struggled with this and so far failed so would really appreciate some help.


I am trying to summarise some data from an organisation chart. The formula needs to find the employee description from anywhere on the worksheet and then return the corresponding headcount from the cell below. I think this might need to use the offset function within the formula but I just can't see the wood for the trees on this one!


I have a sample file if needed but can't see how to show it on here.


Many thanks
 
Hi Aneelaj,


Welcome to the Chandoo.org.


If you wish, you can post a sample file with some examples for us to review. It would be great if you post the file, so that, will get a clear picture of the query.


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


Chandoo, Hui & Team,

Hope what i replied is acceptable to the forum:)


Rgds,

KK
 
Thanks very much - I have uploaded the file here:


https://docs.google.com/open?id=0ByVXXmmtP2N5UnBYMGFuYzctNnc
 
Hi,

Interesting problem...


Since I am not able to see what you uploaded (due to firewall restrictions on my side), here is an approach based on my interpretation that the employee data can be found anywhere on the worksheet.


Assuming that the data range on the worksheet is named "orgdata", and assuming that the employee description is in cell A1, then the following formula would return the value right below where the search criteria is found:

=OFFSET(orgdata,SUMPRODUCT(ISNUMBER(MATCH(orgdata,A1,0))*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))), MATCH(A1,INDEX(orgdata,SUMPRODUCT(ISNUMBER(MATCH(orgdata,A1,0))*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))),0), 0)-1,1,1)


Essentially, the formula finds the row and column where the search value is located, and uses the OFFSET function to get the value from the cell below it.


The row is found using the following segement of the formula:

SUMPRODUCT(ISNUMBER(MATCH(orgdata,A1,0))*ROW($A$1:INDEX($A:$A,ROWS(orgdata))))

It uses match to locate the value, then multiplies it by the number of rows in the range, to determine the row.


Then the formula uses the row information, and locates the column using the following segment:

MATCH(A1,INDEX(orgdata,SUMPRODUCT(ISNUMBER(MATCH(orgdata,A1,0))*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))),0), 0)


Hope this helps.


Cheers,

Sajan.
 
Hello,

Here is a shorter formula that uses a similar approach as the previous post:


=OFFSET(orgdata,SUMPRODUCT((orgdata=A1)*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))),SUMPRODUCT((orgdata=A1)*COLUMN($A$1:INDEX($1:$1,COLUMNS(orgdata))))-1,1,1)


In both formulas, you could use IFERROR() to trap the error returned if a value is not found in the range.


Cheers,

Sajan.
 
Hi Sajan,


Thanks for this. I think the only problem here is that I don't know which cell the matched value will be in i.e. it could be in B65 or J23 so not sure how I would get the MATCH function to identify the cell when the cell reference is not A1.


Does that make sense?


Thanks
 
Hi,

The formula only expects the search value (represented by A1 in the formula). It will then determine where in your data range that value is found.


For example, if you are searching for "Marketing", and that value is in cell A1, then the formula will return the data value from the cell below where "Marketing" is found.

(If the search value is in some other cell, replace "A1" with the other cell reference.)


Hope that makes sense.


Cheers,

Sajan.
 
Hi aneelaj,

Here is a longer explanation of the formula... (I was running between things earlier... so I had to be brief!)

Perhaps SirJB7 would be kind enough to update the uploaded worksheet with this or another formula for you...


If the value being sought (e.g. "Marketing") is in cell B2, then the formula would be as follows:

=OFFSET(orgdata,SUMPRODUCT((orgdata=B2)*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))),SUMPRODUCT((orgdata=B2)*COLUMN($A$1:INDEX($1:$1,COLUMNS(orgdata))))-1,1,1)


If your worksheet has organizational data in the cells A1:X500, the Name ("orgdata") would refer to that data range.


The first parameter for the OFFSET function can take a single cell reference, or a range. For convenience, you could refer to the whole data range. (If the whole range is referenced, the height and width parameters need to be set to 1, as shown in the formula, since you only require a single cell value returned.)


The formula segment SUMPRODUCT((orgdata=B2)*ROW($A$1:INDEX($A:$A,ROWS(orgdata)))) determines the row where the matching value is found. It compares the "orgdata" range to the value being sought (in this case, the value in cell B2). That would return an array of "TRUE" and "FALSE" values.

This formula assumes that only one match would be found. (If your data might have multiple matches for the value being sought, we can adjust the formula using a MAX or MIN function.)


Assuming there can only be up to one matching value in the data range, there should only be a single "TRUE" value returned by "orgdata=B2".

The segment "ROW($A$1:INDEX($A:$A,ROWS(orgdata)))" generates an array of 1..n where n is the number of rows in the data range.

By multiplying the array of "TRUE" and "FALSE" values (that are ordered in the same layout as the data range), with the integer array, the "FALSE" values will be changed to zeros, and the "TRUE" value will be changed to the corresponding number in the integer array. (Again, this works because the "TRUE" and "FALSE" values are arranged in the same row/column sequence as the data range.)

The result is an array such as {0,0,0;2,0,0;0,0,0}.

The SUMPRODUCT function returns the sum of all the values (in this example, 2).


The second segment of the formula "SUMPRODUCT((orgdata=B2)*COLUMN($A$1:INDEX($1:$1,COLUMNS(orgdata))))" does a similar calculation, but determines the column where the matching value is found.


Once the row and column info are obtained, the use of the OFFSET function is straightforward.


I hope this explanation helps clarify how this approach might be used. (Of course, there might be cleverer ways to calculate the "headcount" value. Hopefully, this formula gives you one starting point.)


Cheers,

Sajan.
 
@sthomas

Hi!

Me, being kind enough to do something alike? Am I ill? Should I need to go to the doctor? :)

I still can't access the uploaded file so... I'm still the kindest guy in town...

Regards!
 
Hi SirJB7,

JB never falls ill (since he can't afford to be ill)! So I am sure you are as fit as a fiddle, and kind to boot!!


While your kindness is legendary, in this case, I am guessing that the poster might have gone home for the day (or weekend)!


Regards,

Sajan.
 
Thanks Sajan


Will try the formula over the weekend and let you know how I get on.


SirJB, I have made my file public now (you learn a new thing or two every day!) so hopefully you will be able to view now!


Thanks all appreciate the help.


Regards

Aneela
 
Hi, aneelaj!


Following shtomas orders and having you agreed to give access to your file, I had no choice that to do as being told.


Here's the link to the updated file:

https://dl.dropbox.com/u/60558749/Match%20data%20that%20is%20not%20in%20a%20list%20and%20retunr%20te%20cell%20value%20below%20-%20Sample_Headcount%20%28for%20aneelaj%20from%20sthomas%20at%20chandoo.org%29.xlsx


Just advise if any issue... to shtomas! :)


Regards!
 
Hi SirJB7 and sthomas,


You can reduce the formula further by taking into account capabilities 2 versatile (and non volatile formulas)


=INDEX(OrgData,SUMPRODUCT((OrgData=B16)*ROW(OrgData))-1,SUMPRODUCT((OrgData=B16)*COLUMN(OrgData))-1)
 
Hi shrivallabha,

Excellent formula reduction!


Since I dont have a way to check Excel until Monday... Does ROW function return numbers starting with 1 regardless of location of data range? e.g. if data range was C3:G45. If that works, that would be a great simplification, and I will use it going forward!


I had initially considered replacing OFFSET with INDEX but found that OFFSET works beyond the data range while INDEX is limited to the data range. As such, depending on the specific situation, OFFSET may be necessary. However, a modification to the data range to be able to use INDEX would be much better!


Thanks on the improvement to the formula! I love concise formulas!


Hi SirJB7,

Thanks for supplying the worksheet updates. Your kindness lives on!


Regards,

Sajan.
 
@Sajan

Hi!

Welcome to this forums... ha, ha, ha...

If you happen to see your lookalike sthomas please tell him that I obeyed his orders textually.

Regards!
 
Hi Sajan,


I just merely tried to simplify the situation. You were the one who did the hard part. Well done.


1. The ROW function evaluates the absolute ROW number if supplied with correct range argument. i.e. =ROW(B3) will always evaluate to 3. The interesting part with ROW function is that the range argument is optional. So =ROW() also works and evaluates the row number of the cell in which it resides. However, it makes the formula (no argument case) volatile. You will find following link useful:

http://www.decisionmodels.com/calcsecretsi.htm


2. So coming to our case the formula needed to adjust the ROW evaluation which is absolute while INDEX positions any element in its array through relative position. So we can overcome this hurdle using two options.


2a. By adjusting absolutes to relative position (as demoed above)

=INDEX(OrgData,SUMPRODUCT((OrgData=B16)*ROW(OrgData))-1,SUMPRODUCT((OrgData=B16)*COLUMN(OrgData))-1)

OrgData refers to :$B$3:$F$10

SUMPRODUCT((OrgData=B16)*ROW(OrgData))-1 : It'd have been -2 (as Orgdata starts on row 3) if it were to return actual data but we need to get data on the next row of match so -2 + 1 yields -1. Similarly Orgdata starts on 2nd column so we subtract 1 from evaluated column.


2b. By making INDEX array start from Top Left i.e. from A1.

=INDEX($A$1:$F$10,SUMPRODUCT((OrgData=B18)*ROW(OrgData))+1,SUMPRODUCT((OrgData=B18)*COLUMN(OrgData)))

which is very simple to understand as it refers to foundROW + 1 and refers to foundCOLUMN straight.


3. I don't see any problem with INDEX limiting Array as you have stated here:

Code: 
As such, depending on the specific situation, OFFSET may be necessary. However, a modification to the data range to be able to use INDEX would be much better!

as you could (nearly always) do what you've stated in the 2nd part of the statement as OFFSET is volatile and will give performance hit when used frequently.
 
Hi shrivallabha,

Thanks for the link to the info regarding volatile and non-volatile functions. It was interesting to note that Excel would evaluate an INDEX function even when the specific row and col parameters do not change. But since the evaluation happens just once, perhaps that is OK.


I did get a chance to test the ROW(orgdata) value, and as I was suspecting, it did return the absolute column and row numbers. As such, unless the orgdata is not likely to change to a different range, it would be better to use the previously posted approach (ROW($A$1:INDEX($A:$A,ROWS(orgdata)))) to generate an array of integers starting with 1. However, as you point out in 2b, the formula can be greatly simplified by starting the orgdata range at cell A1.


Hopefully, this gives the original poster a couple of options for structuring the formula and data range.


Hi SirJB7,

Thanks for the welcome!


Regards,

Sajan.
 
Hello all,


Thanks alot for the responses. Apologies for the delay but was out of the office last week. I have tried the formula in my file and I'm now coming up with the message that "Excel has run out of resources while attempting to calculate one or more formulas".....


My file is not particularly large and the data range for lookup only spans about 150 rows and 20 or so columns. I have checked the file for external references - none - error values - none. I have amended the formula so that it is looking up a smaller reference range i.e. A1:A150 rather than A:A and A1:U1 rather than 1:1.


Still not working. I need to work through this in a new file and see whether there is something else I am doing wrong.


Will let you know....


Thanks

Aneela
 
Hi aneelaj,


If the file is small then please upload file with problem, it will be easier to assist.


Regards,
 
You must log in or register to reply here.
Back
Top