Embarrassing question about lookup.

[Emil]

I am attempting to create what should be a simple formula. I am sure there
are better ways to do this but I am somewhat new so just going for what I
know how to use. I have attempted VLOOKUP, but I get a bit confused on this.

D5 is a drop down menu which is the name. (This Cell never moves).
c5,c6,c7, etc… is a numerical value I would like my new target cell to show.
I only have 27 names.

Here’s my formula
=LOOKUP(D5,{"name1","name2","name3","name4","name5","name6","name7","name8","name9","name10","name11","name12","name13","name14","name15","name16","name17","name18","name19","name20","name21","name22","name23","name24","name25","name26","name27"},{"c5","c6","c7","c8","c9","c10","c11","c12","c13","c14","c15","c16","c17","c18","c19","c20","c21","c22","c23","c24","c25","c26","c27","c28","c29","c30","c31"})

I think this formula is perfect except instead of showing “c5â€, etc… I want
it show the value of C5.

Thanks in Advance

 

[Joel]

This should work. It is a formula array so you have to type Shft-Cntl Enter
to get the square brackets

{=OFFSET(C5:C31,MATCH(TRUE,("name"&ROW(A1:A27)=D5),0)-1,0)}

The line below produces an array {"name1","name2,...,"name27"}
("name"&ROW(A1:A27)

The Match returns the index number of the item that matches D5

Then Offset get the item in C5:C31 using the index returned in the Match
function.

 

[Stefi]

=LOOKUP(D5,{"name1","name2","name3"},C5:C7)
Extend it to 27 names!
But I suggest to store the 27 names in a cell range, say B5:B31,
then the formula is:
=LOOKUP(D5,B5:B31,C5:C31)

Regards,
Stefi

„Emil†ezt írta:

 

[Emil]

Stefi:

Wow, that was soo easy! Worked liked a Charm** I can't thank you enough
- I have literally spent hours attempting to figure this out!

Also thank you Joel – I have a lot to learn and you have given me some
direction on what other functions I should know.

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Emil†ezt írta: