Query cells in a row to match in another table to return a true false statement.

[stacilou]

Hey ya'll,

I'm trying to summarize some Line-Point Intercept data and would like to able to summarize it by plant functional group.

[table="width: 500, class: grid"]
[tr]
[td]pt[/td]
[td]Top Canopy[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]Soil Surface[/td]
[td][/td]
[td]Shrub[/td]
[td]Perennial Grass[/td]
[td]Annual Grass[/td]
[td]Perennial Forb[/td]
[td]Annual Forb[/td]
[/tr]
[tr]
[td]1[/td]
[td]QUGA[/td]
[td]POPR[/td]
[td]ACMI2[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td]True[/td]
[td]True[/td]
[td][/td]
[td]True[/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]QUGA[/td]
[td]POPR[/td]
[td]l[/td]
[td][/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]l[/td]
[td][/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]6[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]7[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]8[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]9[/td]
[td]QUGA[/td]
[td]SYOR2[/td]
[td]POPR[/td]
[td]l[/td]
[td]s[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

The second row is what I would like the whole table to look like. Like if there are two shrubs, I would like it to just return a True value, same with Perennial and Annual grasses/forbs. I have a Master species sheet with all the plant codes and duration, or I have a master species list that has all the plants separated by functional group (Shrub, Perennial Grass, Annual Grass, Perennial Forb, Annual Forb).

My first thought was to separate the master species into functional groups, then use those lists. I would use a lookup or match function to look in the entire row, then return a True or False; or a 1 or 0. Example row 2 in the table. Any suggestions?

Thank you!

 

[mrshl9898]

The way i'd usually do this is VLOOKUP and LEN.

so the VLOOKUP will return the code "POPR" for example and LEN will confirm it's there.

=VLOOKUP(C2,'Master species sheet'!A:A,1,0) to get "POPR"

and

=IF(LEN(VLOOKUP(C2,'Master species sheet'!A:A,1,0) )>1,"True","")

The LEN of the return being 4, which is greater than 1, therefore True. If not found it will be #N/A! so the IFERROR changes that to a blank.

=IFERROR(IF(LEN(VLOOKUP(C2,'Master species sheet'!A:A,1,0) )>1,"True",""),"")

 

[stacilou]

Do you know of anyway I could look up the row? I would like excel to search B2:F2, look in in the Shrub Functional group and return "True" or "1", I could then use the same formula, just changing the Functional Group list to match from. Does that make sense and is it possible? Haha!

 

[mrshl9898]

So you'd like to see if any value in B2 or F2 is in another table? There would be an array method and i'm sure someone else will assist there.

A possible workaround is just adding another vlookup to the error, and again etc.

=IFERROR(IF(LEN(VLOOKUP(B2,'Master species sheet'!A:A,1,0) )>1,"True",""),IF(LEN(VLOOKUP(C2,'Master species sheet'!A:A,1,0) )>1,"True",""))

=IFERROR(IFERROR(IF(LEN(VLOOKUP(B2,'Master species sheet'!A:A,1,0) )>1,"True",""),IF(LEN(VLOOKUP(C2,'Master species sheet'!A:A,1,0) )>1,"True","")),IF(LEN(VLOOKUP(D2,'Master species sheet'!A:A,1,0) )>1,"True",""))