Have Excel Look at Three Values and Input Text Of One of Them

**griffin555** · 01-20-2013, 12:01 PM

Hello,

I have data in three columns, and 100 rows of data. Like so:

Sunshine 2 3
Moon 1 2

Where Sunshine is in one column, 2 in another, 3 in another.

I want Excel to make a new display using those values. This display has:
1 2 3 4

Sunshine
Moon

What I want Excel to do is: in the cell below "1" look to see the test next to it, in this case "Sunshine" then look and see if there is a "1" in the original data set in the second position. In my example, the number is 2, and if there isn't a 1, I want excel to input "n/a". Then in the cell below "2" I want excel to look for the text in the column two cells to the left, still Sunshine, then look up and see if there is a 2. If there is, I want it to input the value 3.

Some of my data has 12 variables, so for example
Stars 1 4
Stars 2 6
Stars 3 8

and would want excel to make something that looked like
1 2 3 4 5 6 7 8
Sunshine na 3 na etc
Moon 2 na etc
Stars 4 6 8 etc

Is there any way to do that? I am new to excel and just now learning about IF functions, so I want to ask because I think it might take me awhile to figure out on my own. I will return the favor!

**griffin555** · 01-20-2013, 12:04 PM

oh, the formatting didn't hold in the post, if it's unclear, please ask, or i can attach a sample

**bebo021999** · 01-20-2013, 12:11 PM

Yep, it is better to view an Excel file to make it clearer

**griffin555** · 01-20-2013, 12:43 PM

Here is an example of the raw data, and how I want to appear!

**bebo021999** · 01-20-2013, 01:14 PM

The example helps much to gives solution!
In B12:

=INDEX($B$2:$B$9,MATCH($A12&B$11,$C$2:$C$9&"Season "&$A$2:$A$9,0))

Confirmed with Ctrl-Shift-Enter rather than just Enter

**griffin555** · 01-20-2013, 04:54 PM

Amazing! Thank you for the super-fast response.

In what cell do you input the formula to have it manipulate the data? I am really a super-novice -- what is great about your solution, though is I can reverse-engineer what you did to learn the functions and how they operate. That said, it isn't clear to me yet, so if you want to offer any additional information about where to put the function, I will appreciate it.

**griffin555** · 01-20-2013, 04:57 PM

Okay, I just figured out to put it in b12...stay tuned...

**griffin555** · 01-20-2013, 05:00 PM

Wow, that's the coolest magic trick ever! Thank you, and you have motivated me immensely.

**griffin555** · 01-20-2013, 05:05 PM

Wait, I am back to the same question -- if I paste the formula into B12 I get "#VALUE!", if I paste into A12 I get "0"

**bebo021999** · 01-21-2013, 03:57 AM

Originally Posted by griffin555

Wait, I am back to the same question -- if I paste the formula into B12 I get "#VALUE!", if I paste into A12 I get "0"

This is an array formula so you must confirm by holding both Ctrl-shift then press Enter.

**bebo021999** · 01-21-2013, 12:13 PM

Nice to hear that you are happy with my help

1- The pair of curly bracket appear on formula bar to indicate that is array formula, not from typing directly, but from Ctrl-shift-enter. If you press F2 to edit, there are not any curly bracket, right?
2- If you have next season (5,6,7,...) next columns, try this in B12:
=IF(ISERROR(MATCH($A12&B$11,$C$2:$C$9&"Season "&$A$2:$A$9,0)),"Your Text",INDEX($B$2:$B$9,MATCH($A12&B$11,$C$2:$C$9&"Season "&$A$2:$A$9,0)))
means if there is no match, return Your Text, if there is, show the match.
3- to generate the list of unique shows where , i.e from A17:
A17=IF(ISERROR(MATCH(0,COUNTIF($A$16:A16,$C$2:$C$9),0)),"",INDEX($C$2:$C$9,MATCH(0,COUNTIF($A$16:A16,$C$2:$C$9),0)))
Ctrl-Shift-Enter
Drag down as far as you want!

Other tip to understand my MATCH in #5, cell B12:
The criteria: A12&B11:="Charlie's AngelSeason 1"
to search in combination of C2:C9&"Season "&A2:A9={"Charlie's AngelSeason 1","Charlie's AngelSeason 2","Charlie's AngelSeason 3",......,"CHEYENESeason 5"
returns position in the list. Use INDEX for column D with this number of row to find the corespondent value
Hope this is clear for you now

**griffin555** · 01-21-2013, 03:04 PM

Hello,

Thank you!

I am studying it! Will write once I've gotten a bit more.

Thanks again,

Have Excel Look at Three Values and Input Text Of One of Them

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Re: Have Excel Look at Three Values and Input Text Of One of Them

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions