Retreive information from specific columns

**Canuck.** · 08-08-2009, 09:38 PM

Good day, I am new to this forum so if I have places this in the wrong area I apologise. I am looking for a formula to retrieve the latest data within a row over four columns.

Each column has a date header, with data associated with the date. The may be data, there may not be data. For each row I am trying to pull out the latest data.

For example

Row 1 has data in column 1, and column 3 but not column 2 and 4.
Row 2 has data in column 1, and column 4 but not column 2 and 3.
Row 3 has data in column 1, and not in column 2, 3 or 4
and the permutations continue.......

To the left of column 1 for each row I would like to display:
Row 1 Column 3 data
Row 2 Column 4 data
Row 3 Column 1 data

All data are numbers, where no data is represented as null or "nothing" values.

Any help in this matter is appreciated.....

Cheers

**JBeaucaire** · 08-08-2009, 10:00 PM

PHP Code:


Data    1/1/2009    1/2/2009    1/3/2009    1/4/2009
e         d                      e    
x         r                                  x
m         m            
r                     l          r    
a                     l                      a
m                                p           m
z                                            z

The formula in A2 then copied down would be:

=LOOKUP(2,1/(B2:E2<>""),B2:E2)

**Canuck.** · 08-09-2009, 08:42 PM

Thanks you very much for this help. For my own learning (and if you want to) can you please just walk me thought the formula - particularly what the "1/" means.

Regards

**JBeaucaire** · 08-09-2009, 10:43 PM

Highlight the cell, click on the Evaluate Formula icon on the Formula Auditing toolbar. Step through the formula one instruction at a time, this is a very informative tool. As you step through, each formula "piece" will unfold and you will see how the pieces interact to reach the answer.

**Canuck.** · 08-10-2009, 03:26 AM

I tried the foumal evaluate (cool tip didnt know it existed) but still a bit hazy on how this works...

the 2 represents? In the help bar is only talks about what to look up for. I can not understand how the value two states the last value.
I am also unsure around the 1/ part is unknown - when I take it out i get a N/A - i am assuming that you a purposly tring to get a div o error then omit the zeros?

All in a learning days work I guess......

**JBeaucaire** · 08-10-2009, 09:54 AM

Originally Posted by Canuck.

the 2 represents?

In a standard LOOKUP() formula, the first parameter is the value you're trying to find in the SearchArray. The second parameter is the SearchArray. The third (optional) parameter is the ReturnArray to bring a value from in the same position the value was found in the SearchArray.

=LOOKUP(Value,SearchArray,ReturnArray)

In the help bar is only talks about what to look up for. I can not understand how the value two states the last value.

Also, in a LOOKUP, if the searched value is NOT found, the last value in the array that is closest without going over is returned as the answer. If you searched for 100 and 50 was the highest values in the sorted array, you'd get the answer of 50. Keep that in mind.

I am also unsure around the 1/ part is unknown - when I take it out i get a N/A - i am assuming that you a purposely tring to get a div o error then omit the zeros?

Sort of, in reality the #DIV/0! errors equate to a "not found" answer and are ignored, not omitted. Their position in the original array is still important as "not match" values.

This formula has several pieces that unfold in a specific order.
=LOOKUP(2,1/(B2:E2<>""),B2:E2)

First up is:
=(B2:E2<>"")

That returns an array of true/false answers. {TRUE,FALSE,TRUE,FALSE} in the first row of the sample data. Keep in mind that Excel treats True as 1 and False as 0 if you try to do math on those values.

Next up is:
=1/(B2:E2<>"")
...which we now know equates to:
=1/({TRUE,FALSE,TRUE,FALSE})

Since it's an array, we're going to get an array of answers when we try to divide 1 by those true/false answers:
{1,#DIV/0!,1,#DIV/0!}

At this point our original formula:
=LOOKUP(2,1/(B2:E2<>""),B2:E2)
...has now evaluated down to:
=LOOKUP(2,{1,#DIV/0!,1,#DIV/0!},B2:E2)

According to what we know about LOOKUP(), the closest value that doesn't go over our searched value of 2 is 1, and the position of the last 1 is position 3 in the first array (underlined above).

So now LOOKUP is supposed to return the 3rd value in the second array.

**Canuck.** · 08-11-2009, 05:54 AM

Thank you for taking the time to explan that - I really appreciate it. I will have a glass of vino on you behalf tonight to thank you for your help.

Regards