What to Use VLOOKUP - HLOOKUP?

[tech247]

Hi - Hope someone can help.
I hope this is pretty simple
I have two rows of data on sheet 1
A B C D E
1 100 200 300 400 500
2 no yes no no no

On sheet 2 I would like to report the value for example b1 200 based on the b2 yes.

I am thinking the an hlookup would work but have had no success. Is it that I am trying to "look up" and not "down"?

Is it just that it is two rows?
Is there another formula that would work better?
FYI I have 14 columns or I would us nested ifs.

Appreciate any help.

 

[mintjulep]

Yes, hlookup wants to "look down", but that's not your only problem.

The lookup functions need to look at a range of UNIQUE values. They will work with a non-unique list, but in that case they will always return something based on the first cell that matches the criteria.

If you were looking for "no", how could the function know which of the four "no"s to act on?

 

[tech247]

mintjulep thanks for the quick reply...

Hmmm wants to look down - thought so.

If forgot to mention that I am always looking for "yes" and there should never be more than one.

 

[SkipVought]

Hi,

NEITHER.

Both VLOOKUP & HLOOKUP require that the lookup range be the FIRST Column or Row in the range.

As a matter of practice, almost always use INDEX & MATCH...
[tt]
=INDEX($A$1:$E$1,1,MATCH("yes",$A$2:$E$2,0))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

and there should never be more than one

What will prevent it?

You should never rely on things that should never be. If you do you WILL get unexpected results at some point.

 

[tech247]

Skip

Thanks so much - you hit the nail on the head!

tech247

 

[tech247]

mintjulep

You are right and the method that I am using is simplistic and relying on end users to enter the "right" information is asking for trouble.

At this point, I am just trying to prototype a concept. Error checking is definitely required.

I have not used Tek Tips for ages, but you both have made me a believer!

 

[SkipVought]

You ought to use 5 option buttons. If GROUPED, they are mutually exclusive: only ONE can be selected.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Why "lookup" anything if there is only one right answer?

It's not clear what you really want to accomplish, but it appears that you want to associate a specific value with something else being "yes".

So why not simply have one cell where the user enters that value? Say that cell is A1.

Then

=if(something = "yes", A1, whatever)