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,B2200,2,False)

--
HTH

Bob Phillips

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

 

[Ardus Petus]

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

HTH

 

[arnoberg]

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

@ Bob Philips: Youre solution would be correct if i would only like th
values of the second column. What happens when i fill D4 in cell1. The
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

 

[daddylonglegs]

Assuming your data is in B12 and your lookup value in H1 try

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

confirmed with CTRL+SHIFT+ENTER