I know there has to be a very easy way to do this, but I can't figure it out. I want to have Excel search for a horizontal value, then a vertical value, then return the number that corresponds to their point of intersection. So in the picture below, if I wanted to find the distance between Albany and Catskill, the equation should return 34. Thank you very much for any help.

I am doing this for a graduate design project, and if you would like, I will gladly acknowledge your assistance in my final paper.

As an added bonus, I will buy a three month Premium subscription for the first user who posts an answer that ends up working out for me (or if he/she is already a preemie, a friend of his/her choosing). I don't know if there is a built in gifting feature, but I am assuming qntmfred can help out otherwise.

9/20/2011 10:47:13 PM

dibs on the preemie...

9/20/2011 10:51:53 PM

something like this seems to be setting it up, but i'm not familiar with the functions:

http://www.ozgrid.com/forum/showthread.php?t=86624&page=1

[Edited on September 20, 2011 at 10:57 PM. Reason : actually try this one: http://www.contextures.com/xlfunctions03.html]

9/20/2011 10:52:32 PM

How do you input values? Are they by city name? Does this text exist in a cell or is it entered through a form?

How do you want the result? in a cell, or as a dialog box?

9/20/2011 10:53:33 PM

i've got an example setup, pm me your email address

the gist of my answer is:

insert a new column to the left of column B, in this new column type in the row number (so B2 = 2, B3 = 3, B4=4, etc...)

then you can use a combination of hlookup and vlookup like below:

=HLOOKUP(<cell containing row 1 value to lookup>,A1:E5,VLOOKUP(<cell containing column a value to lookup>,A2:B5,2,FALSE),FALSE)

9/20/2011 10:55:15 PM

i did this once in a budgeting spreadsheet. it was a pain it the rear to add new rows/columns though so i eventually wrote a proper app to do it

[Edited on September 20, 2011 at 11:05 PM. Reason : and yes, there is a premie gifting feature]

9/20/2011 11:05:11 PM

I've got one similar

I want to search a spreadsheet for a value. Then have it output the value in the column to the right along with my search value.

9/20/2011 11:09:17 PM

Can you post a small example

9/20/2011 11:10:52 PM

^^

=C1&" : "&VLOOKUP(C1,A1:A100,2,FALSE)


[Edited on September 20, 2011 at 11:11 PM. Reason : search set is A1:A100, search value is C1]

9/20/2011 11:11:04 PM

Often said of me is that I am a good one friend of choice.

Just putting that out there to premies with Excel knowledge.

9/20/2011 11:14:37 PM

I'm on my phone and don't have an example to post. But I'll try to type out an example, just translate it to a spreadsheet format in your head.


Aaaaa 1
Bbbbb 2
Aaaaa 3
Bbbbb 4


I want to search for Aaaaa and it come back with:
Aaaaa 1
Aaaaa 3

Usually I just do a Ctrl-f "Aaaaa" and it shows me the 2 cells. But I want to know the value in the next column.

9/20/2011 11:15:26 PM

OK, HaLo definitely wins this. I appreciate the participation/links but the actual formula was nothing like what I was doing with the Index function. Perhaps the Index function could be used, but I was unable to figure it out (I had been trying it for a good 30 minutes before I made this thread)

I am going to try to order the Premium Membership now. I have never done this before so I might need some additional info, so stay near your Inbox.

9/20/2011 11:17:35 PM

Yes you could use A combination of the index function with the match function as well. Slightly more "elegant" doesn't require inserting the column with row numbers.

9/20/2011 11:23:42 PM

I sent an pm to qntmfred. Waiting for a reply.

Right now the website defaults to giving the Premium Membership to my account. Whenever qntmfred gets back to me, I will transfer the money.

If for some reason this doesn't work, Option B is that I just send you the funds via PayPal and you order the membership yourself.

[Edited on September 20, 2011 at 11:30 PM. Reason : qntm]

9/20/2011 11:29:39 PM

Index / Match, FTW!

9/20/2011 11:30:53 PM

xxxx32-1a-yyy bbbba32 1 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 1 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 2 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 2 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 3 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 3 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 4 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 4 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 5 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 5 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 6 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 6 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 7 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 7 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 8 xxxx36-1a-yyy bbbba36 1
xxxx32-1a-zzz bbbba33 8 xxxx36-1a-zzz bbbba36 2
xxxx32-1a-yyy bbbba32 9 xxxx36-1a-yyy bbbba36 1



There's an example. I'd like to search for each instance of xxxx32-1a-yyy and find the value in the column with the number

So the output would be

xxxx32-1a-yyy 1
....
xxxx32-1a-yyy 9


Qfreds formula didn't work. Or I'm too dumb to figure it out.

Supposed to be 2 sets of data each with 3 columns.

[Edited on September 21, 2011 at 10:06 AM. Reason : .]

9/21/2011 10:04:00 AM