Lookup Formula

[jpxexcelforum]

Hi.

I hope someone can help.

I need H2 to lookup datum in Column B. The hard part is the lookupvalue and the datum is in one column

Please see attachment for more information.

I appreciate all the help.

[jeffreybrown]

How about...

H2 =LOOKUP(2,1/(B4:B14="Log In"),C4:C14)
H3 =LOOKUP(2,1/(B17:B28="Log In"),C17:C28)

[jpxexcelforum]

How about...

H2 =LOOKUP(2,1/(B4:B14="Log In"),C4:C14)
H3 =LOOKUP(2,1/(B17:B28="Log In"),C17:C28)

Hi Jeff,

Thanks for the prompt response.

Is there a way I can copy paste a formula all the way down in case I have more than 30 stations to look up so I won't have to edit the ranges for every station.

[jeffreybrown]

Nothing immediately comes to mind except for, why not rearrage your blocks of data so they go left to right instead of up and down.

With this setup you can easily transfer the formula to the next block of data.

[jpxexcelforum]

Nothing immediately comes to mind except for, why not rearrage your blocks of data so they go left to right instead of up and down.

With this setup you can easily transfer the formula to the next block of data.

I get you mean. Unfortunately, the list given me goes up down. To do it manually would take forever.

[NBVC]

You can convert Jeff's formula in H2 to:

Please Login or Register  to view this content.

copied down, if your groups are consistent in size and position.

[jpxexcelforum]

Thanks. This is perfect. Last question is how can I look up values in A instead of C?

[NBVC]

Set the columns in the second offset to -1 to look to the left of B...

Please Login or Register  to view this content.

[jpxexcelforum]

Set the columns in the second offset to -1 to look to the left of B...

Please Login or Register  to view this content.

Thanks! Work Great!. I just found out that the groups are not consistent in size and position. Some groups go beyond 200. Any idea how this could work?