MS Excel, search column A and get data from B

June 1, 2011 at 21:05:27
Specs: Windows XP
Hello!! First, thank you all in advance for any advice you are able to provide.

My dilemma: Using MS Excel, I have been attempting to write a formula that will search for a value in a row and return the value in the cell next to it, no matter how the data it is sorted. I have not had any luck and I'm hoping you all wouldn't mind providing some guidance.

Example:
01 139.0
02 59.2
03 107.0
04 140.2
05 200.0
06 -5.6
…through 27.

I would like to extract the information in column B by searching for a specific value in column A. For example, if I search for 03 I would like it to return 107.0. Writing that formula would be simple if it were not for needing to change the way the data is sorted, either by aircraft number or by aircraft hours depending on the situation.

05 200.0
04 140.2
01 139.0
03 107.0
02 59.2
06 -5.6
…through 27.

The formula would always search for values in column A (01, 02, 03, etc.) and return the correlating value in column B. The trouble I’m having is the location of the value I am searching for changes rows depending on how it’s sorted.

My overall goal is to create three graphs with this information. I will use data for all 27 numbers to create the first graph, 1 through 9 to create the second, and 10 through 29 to create the third.

Hopefully this wasn’t an overload of information! I wanted to provide as much as possible just in case someone wanted to suggest another route. Any advice would be greatly appreciated and thank you all in advance!!

Nate (USMC)


See More: MS Excel, search column A and get data from B

Report •

#1
June 2, 2011 at 14:06:00
If your data looks like this:

   A        B
1) 5       200
2) 4     140.2
3) 1       139
4) 3       107
5) 2      59.2
6) 6      -5.6
7)	
8) 2      59.2

In cell B8 enter the formula: =SUMPRODUCT(--($A$1:$A$6=$A8),(B1:B6))

In cell A8 enter the number to search for.

How you have the data sorted should not matter.

Just be aware that you cannot use 01, 02, 03 as numbers in column A,
Excel will strip the leading zero off and you will end up with 1, 2, 3.

To retain the leading zero you must have column A formatted as TEXT.

It will only cause problems when you enter a NUMBER to search for and the target cells are TEXT or vice versa.

MIKE

http://www.skeptic.com/


Report •

#2
June 2, 2011 at 14:51:20
Just so we are clear, you can retain the leading zero's on your numbers if necessary, simply do a Custom Format on your range of cells.

If you are using 4 digit numbers then a Custom Format of: 0000
If you enter just the number 1, you will get 0001
If you enter just the number 11 you will get 0011
If you enter just the number 111 you will get 0111

My point was to be aware of mixing Text & Numbers.

MIKE

http://www.skeptic.com/


Report •

#3
June 2, 2011 at 16:27:15
Look at that, fantastic! Thank you very much for the help!! I can piddle with the numbers/text as I need, that's no problem. I searched for days to get something like this. You just saved me tons of man hours while out here and those are far too scarce in this environment! Thanks again!!

Nate


Report •
Related Solutions


Ask Question