1

Basically, my formula works exactly how I need it to right now. However, there's still more manual entry than I'd like having to do. It's minimal, but if I can eliminate it altogether it's all the better.

My current formula is =IFERROR(INDEX(RawData[value], MATCH(1, (RawData[plot_id]=$A2)*(RawData[class]=1)*(RawData[metric]=LEFT($B1,FIND("(",$B1)-1)), 0)),0). This can be seen in the Test Organized Tab image below.

I still have to manually change the class number for each formula when I move it to a new column (the class number each column is searching for is the parenthetical number in that column's header: (1), (2), (5)). I've tried replacing RawData[class]=1 with RawData[class]=MID(B1, SEARCH("(", B1)+1, SEARCH(")", B1) - SEARCH("(", B1) -1), but it breaks the formula and will only give me the error result of 0.

However, if I use the same MID (=MID(B1, SEARCH("(", B1)+1, SEARCH(")", B1) - SEARCH("(", B1) -1)) function in a cell on its own, I get exactly the result I'm looking for: the number between the parenthesis in the header cell of the column (which can be seen in cell B4 of the previously mentioned screenshot.

I'm at a loss because it allows me to successfully use the LEFT function when searching for the RawData[metric] criteria.

Any idea what's going on here or how I can get this to work?


Raw Data Table (generic data)

metric class plot_id value
area_mn 1 1 0.720177968
area_mn 1 2 0.005311753
area_mn 2 1 0.011488774
area_mn 2 2 0.435016987
ed 5 1 0.1856209

Screenshot for Visual Reference

Raw Data Image


Organized Data Table (generic data, showing desired result)

plot_id area_mn(1) area_mn(2) ed(5)
1 0.720177968 0.01148877 0.720177968
2 0.005311753 0.435016987 0

Screenshot for Visual Reference Test Organized Tab Image2

3
  • 1
    Have you tried using absolute references in your MID function? So instead of B1 use B$1? Also, don't provide your text as an image. Without any copy-pasteable table someone who wants to help needs to retype your data and formulas. That's quite a big ask. Could you edit your question to include a small example subset? Sep 21 at 10:53
  • @SaaruLindestøkke Edited because I completely misinterpreted your comment before. Completely my bad. I've not tried using absolutes. That may be something to try. And my apologies, I'll edit my post for a more complete/useful read. :)
    – X-Cell
    Sep 21 at 11:24
  • 1
    I believe the issue is the MID formula is a text while your RawData[class] is a number, wrap your MID with VALUE
    – gns100
    Sep 21 at 14:50

3 Answers 3

2

This seems to be an issue with incompatible datatypes in the RawData[class]=1 part.

Presumably class in RawData is a number (no way to know, as you didn't include any data). When you write 1 yourself, Excel understands that it is a number and uses that for the comparison. When you use the MID() formula to extract that number for example from the string area_mn(1), you get back a string that is 1. The number 1 is not equal to the string 1, so the comparison fails.

If you wrap that MID() function in an INT() formula, the string gets converted to an integer (a number) and the comparison works again.

Here is an example:

Data table

id area_mn(1) area_mn(2) area_mn(5) ed(1) ed(2) ed(5)
1
2
3

Lookup table

id value
1 100
2 200
3 300
4 400
5 500

If you use below formula in your data table you'll get #N/A:

=INDEX(lookup[#Data], MATCH(MID(B$1,SEARCH("(",B$1)+1,SEARCH(")",B$1)-SEARCH("(",B$1)-1),lookup[[id]:[id]], 0), 2)

But if you convert the looked up value to an integer first, it works:

=INDEX(lookup[#Data], MATCH(INT(MID(B$1,SEARCH("(",B$1)+1,SEARCH(")",B$1)-SEARCH("(",B$1)-1)),lookup[[id]:[id]], 0), 2)
4
  • 1
    If your class number is always a number in parentheses at the end of text you can simplify your formula to extract it as a number. Try =-REPLACE(B1,1,SEARCH("(",B1)-1,"") or =-MID(B1, SEARCH("(", B1), 10). If you convert text (1) to a number you get negative value. This is why you need to add minus before.
    – MGonet
    Sep 21 at 11:41
  • @SaaruLindestøkke You are correct, wrapping my MID function in the INT function does allow the formula to continue functioning properly; however, that's only true for the first cell I implemented the formula into (B2, in this case). When I fill the formula throughout the rest of the table I get the value I'm looking for returned in a few cells, but mostly the error value of 0, even in cells where my current formula fills correct value. I appreciate the time you took with this, and the INT suggestion helps. I'll edit my original post to be more helpful and will let you know when that's done.
    – X-Cell
    Sep 21 at 11:44
  • 1
    Use $ in addresses where appropriate.
    – MGonet
    Sep 21 at 11:51
  • @MGonet good suggestion, feel free to edit my answer to include it. Sep 21 at 13:01
2

It looks like your table headers are a combination of the [metric] and [class] values from your RawData table. You could probably make things easier on yourself by using a single delimiter, such as a "." or "," or ";" rather than using both opening and closing parenthesis, especially if your [class] values are only single digits. That way you could just use =VALUE(RIGHT(B$1,1)) or =INT(RIGHT(B$1,1)) or =--RIGHT(B$1,1) to extract the number. For example:

plot_id area_mn.1 area_mn.2 area_mn.5
1
2
3

With the headers formatted in this manner, the following formula would work in cell B2:

=IFERROR(INDEX(RawData[value], MATCH(1, (RawData[plot_id]=$A2)*(RawData[class]=VALUE(RIGHT(B$1,1)))*(RawData[metric]=LEFT(B$1,FIND(".",B$1)-1)), 0)), 0)

Note the absolute cell references $A2 (absolute column) and B$1 (absolute row) used above. In your original attempt, you had incorrectly used $B1 (absolute column) on the header row, which is why it wouldn't copy down and across properly.

3
  • 1
    I appreciate the suggestion! Unfortunately this is a project I'm working on for someone else, and they want them parenthetically. But I'll make the suggestion to them because I wouldn't be surprised if they just had thought of something like this—and I'm sure they don't realize how much easier it would be with Excel.
    – X-Cell
    Sep 21 at 17:08
  • 1
    Fair enough. This would work then, with parentheses, if all of your [class] values are single digits: =IFERROR(INDEX(RawData[value], MATCH(1, (RawData[plot_id]=$A2)*(RawData[class]=-RIGHT(B$1,3))*(RawData[metric]=LEFT(B$1,FIND("(",B$1)-1)), 0)), 0). RIGHT is simpler than MID with SEARCH.
    – DjC
    Sep 22 at 1:16
  • After I sent them the file and was explaining how the formula was working, I started wondering if RIGHT was a better option. It significantly reduces the formula length too!! :) I’ve never had to get very complex with Excel. This is actually the first time I’ve used most of these functions. It’s definitely been a valuable learning experience into the options there are and the easiest way to approach a formula like this.
    – X-Cell
    Sep 22 at 11:48
1

Update

After messing with it for awhile this morning, I wasn't able to get the formula to work correctly using exact responses I got—I still keep getting incorrect data when filling the cell across and down the rest of my table, but all of you have been very helpful, and I appreciate your time and help.

For the final formula, I came up with =IFERROR(INDEX(RawData[[value]:[value]], MATCH(1, (RawData[[plot_id]:[plot_id]]=$A2)*(RawData[[class]:[class]]=-MID(B$1, SEARCH("(", B$1), 3))*(RawData[[metric]:[metric]]=LEFT(B$1,FIND("(",B$1)-1)), 0)),0).

@MGonet I appreciate your comment that helped reduce the portion of the formula that was pulling the class number. This thing got long enough as is. Lol.

For additional explanation: I realized my table columns were shifting when I would autofill the data across my table. I didn't realize they would do that, I thought specifying a table column was always an absolute reference—today I learned otherwise. So I corrected those references to make them absolute.

After that, I basically took bits and pieces of all of the advise and suggestions from here (e.g. correcting areas where I typo'd the absolute cell reference I was making, using more condensed function alternatives, etc.), plus my own additional research and some more trial and error, and was able to get a formula that does exactly what I need.

Seriously, thank you all so much for your time!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .