How do i look up data that has a 2 lablesl?

[kevinu]

I running into a problem. I dont know how to get excel to look up data from an array with labels that have 2 names and then pulling that data up. I'm considering Vlookup but it wont find the correct information. It might be how the data is organized too so if that has to change i'm game. Attached is an example file. On sheet one I have all the data. Its scores for data comparing connection between 2 areas of the brain in frequencies such as Delta, Theta, Alpha1, Alpha2, Beta1, Beta2, Beta3 and HighBeta. So the locations have 2 areas. Example would be what are the scores between areas 1 left and 20 left. Its labeled 1L - 20L. There are also 1R - 20R for the right side and scores for connections between 1L and 2L all the way up to 47.

I've tried to Vlookup but it cant seem to distinguish 1L when its labeled 1L - 20L. It just finds all the 1's so it returns values in 21L sometimes. Hope this explains it well. In any case Sheet 1 has the data with the location values in column A and the return values I need in b through I. Sheet 2 is where I want to pull up all the values of whatever i'm looking for such as, give me all the connections with 1L in it or all the values with 1L, 13R and 44L.

Edit: The data comes to me in the format of Sheet 1. If I need to reorganize it i would but would rather find a way to use it as is if possible.

[Richard Buttrey]

Please manually add the results and clearly explain why you choose those answers. It's not clear, at least to me and your second paragraph is very confusing.

Do you always want the same results (assuming the data changes from time to time), or do you want the ability to change the selection criteria?

[protonLeah]

"... I want to pull up all the values of whatever i'm looking for such as..." How do we know what those are? What is the significance of all those colored cells?

[kevinu]

Sorry, Ya I had a hard time explaining it. New file with the example of the results I need on sheet 2 now. Also ignore the colored cells, I just conditional formated it to see values easier. It has nothing to do with my question.

[sandy666]

1L and 8R is always on the first place of the string in column A or can be at the any place?
1L - xxx
8R - xxx
or
xx - 8R
xx - 1L
etc...

[kevinu]

Most of the time it will be the be on the first place of the string but there are some occasions like in 8L where it will be in the second place.

If I was looking for 30L over half would be (xxx - 30L) and half would be (30L - xxx)

Also, not sure if this matters but there are 2 locaions that are not numbers. They are AmyL, AmyR and HipL and HipR. This is for amygdala and hippocampus.

[sandy666]

So what are you lookin' for? 1L and 8R or what? In file is: Example: I want to recall all data from locations with 1L and 8R in its location
I did it, but now I see any 2L, 8L etc.
and I don't know 21L or 18R is correct or no?
Define first your criteria.

[kevinu]

the locations are called Brodmann areas. They are locations in the brain that go from 1 to 47 plus Amy and Hip, L is left side and R is right side. So I want the ability to look for any areas of interest and have it pull up those metrics. I just chose 1L and 8L as an example but I will need to be able to select any or all of them at different times.

Here is a real life example:

I'm looking at the memory network in the brain and I know that research shows the areas of that network are in 7L 7R, 8L, 8R, 9L, 9R, 10L, 10R, 19L, 19R, 20L, 20R, 21L, 21R, 46L, 46R and 47L. I want to pull up all the data (in columns b-i) for all those areas. So anytime any of those numbers appear in column A, I want that data. Whether its in the first place or second place.

If I was looking for attention networks it would be different areas so I need the ability to pick and choose what locations show up. Ideally I want to be able to put in all the locations at once and have it pull them all up, not just one at a time. Hope that helps, sorry if my explanations are vague.

[sandy666]

ok, see att. on result tab

[kevinu]

Nice! However, when I put in a single location like 1L it pulls up things like 21L because the 1 is in there. Is there a way to make it not do that? But thanks that gives me a head start for sure.

[sandy666]

type 1L and space

you can use all wildcards
ie. *, ?, ~
space and of course all characters what exist in col. A of main tab

[kevinu]

Not sure I understand. If I put in an exact location it works wonderful. If i put in a single location like 1L it pulls up stuff that shouldnt be there. I tired 1L and it pulled up things like 21L and 12L. I assume because 1 is in 21. Its not seeing 21 as a unique number, or so it seems to me. Or am i just doing it wrong?

[sandy666]

here is modified a little
You can filter everything even what does not exist there with custom filter

btw. you can use Custom filter directly on your raw data without any formula
caf.jpg

[kevinu]

Thats amazing, thanks a lot this forum is the best thing ever Thank you sooo much

[sandy666]

You are welcome

If the basic problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks