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)

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.2In 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

Just so we are clear, you can retain the leading zero'son 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 0111My point was to be aware of mixing Text & Numbers.

MIKE

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

Ask Your Question

Weekly Poll

Do you think its a good idea for Amazon to divide its new second headquarters between two locations?

Discuss in The Lounge

Poll History