Lookup functions

[arnoberg]

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

[Bob Phillips]

Why not just

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

--
HTH

Bob Phillips

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

"arnoberg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ardus Petus]

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

HTH
--
AP

"arnoberg" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> 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
>

[arnoberg]

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. So in fact we need your formula with a variable column!

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

Could you please help me further?

[daddylonglegs]

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(B1:D2)-COLUMN($B2)))),2,0)

confirmed with CTRL+SHIFT+ENTER.