Hello everyone,

For a couple of weeks i've been trying to create a formula to find a
given value in a big range of numbers and give back the number in the
column next to it on the cell next to the input.

Example:

R1 | C2 | C3 | C4 |
R1 | D1 | D3 | D5 |
R2 | D2 | D4 | D6 |

When I fill D1 in cell 1, I want in cell 2(the cell next to it) the
value D3.

The lookup/vlookup/match/hlookup don't match the citeria, but maybe
there is a possibility to make a combination of those formulas?

I hope the explanation is clear and someone can help me.

Gr. Arie


--
arnoberg
------------------------------------------------------------------------
arnoberg's Profile: http://www.excelforum.com/member.php...o&userid=36187
View this thread: http://www.excelforum.com/showthread...hreadid=559612

Why not just

=VLOOKUP(lookup_val,B2:D200,2,False)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"arnoberg" wrote in
message ...

Hello everyone,

For a couple of weeks i've been trying to create a formula to find a
given value in a big range of numbers and give back the number in the
column next to it on the cell next to the input.

Example:

R1 | C2 | C3 | C4 |
R1 | D1 | D3 | D5 |
R2 | D2 | D4 | D6 |

When I fill D1 in cell 1, I want in cell 2(the cell next to it) the
value D3.

The lookup/vlookup/match/hlookup don't match the citeria, but maybe
there is a possibility to make a combination of those formulas?

I hope the explanation is clear and someone can help me.

Gr. Arie


--
arnoberg
------------------------------------------------------------------------
arnoberg's Profile:
http://www.excelforum.com/member.php...o&userid=36187
View this thread: http://www.excelforum.com/showthread...hreadid=559612

Which result do you want in cell 2 if Cell 1 holds D5
(there's no cell right of D5)

HTH
--
AP

"arnoberg" a écrit
dans le message de news:
...

Hello everyone,

For a couple of weeks i've been trying to create a formula to find a
given value in a big range of numbers and give back the number in the
column next to it on the cell next to the input.

Example:

R1 | C2 | C3 | C4 |
R1 | D1 | D3 | D5 |
R2 | D2 | D4 | D6 |

When I fill D1 in cell 1, I want in cell 2(the cell next to it) the
value D3.

The lookup/vlookup/match/hlookup don't match the citeria, but maybe
there is a possibility to make a combination of those formulas?

I hope the explanation is clear and someone can help me.

Gr. Arie


--
arnoberg
------------------------------------------------------------------------
arnoberg's Profile:
http://www.excelforum.com/member.php...o&userid=36187
View this thread: http://www.excelforum.com/showthread...hreadid=559612

Thnx for you answers, but i'm still not there.

@ Bob Philips: Youre solution would be correct if i would only like the
values of the second column. What happens when i fill D4 in cell1. Then
i will get an error, and in my spreadsheat I want the value D6.

@ Ardus Petes:if cell 1 holds D5 I want no value or 0.

Could please help me further?


--
arnoberg
------------------------------------------------------------------------
arnoberg's Profile: http://www.excelforum.com/member.php...o&userid=36187
View this thread: http://www.excelforum.com/showthread...hreadid=559612

Assuming your data is in B1:D2 and your lookup value in H1 try

=VLOOKUP(H1,OFFSET(B1:C2,,MIN(IF(B1:D2=H1,COLUMN(B 1:D2)-COLUMN($B2)))),2,0)

confirmed with CTRL+SHIFT+ENTER.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=559612