HLOOKUP field in Excel not working

[Isis5898]

This would probably be easier to describe if I could give you the spreadsheet, but here goes.
We have a workbook that contains several sheets. One of them is called topics. In the A column it has (vertically) listed Topic 1, Topic 2 and so on. In column B it has a description of each topic. We want the HLOOKUP to exsit in the C column. We want the lookup to use the A column and reference another sheet tab, AGE Data and match each topic, then return row #19. Sounds simple enough but I can't get it to work with the A column being vertical. When I transpose the information to a horizontal format (which I don't want) it works fine. Can anyone help? Is there a better function? Is there anyway to have the topics in column A separated with additional text?

I can usually make this stuff work, but am frustrated now.
Thanks!
Jenna

 

[AnotherHiggins]

HLookup = Horizontal Lookup
VLookup = Vertical Lookup

Use VLookup instead.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Isis5898]

But the value that I am trying to return is horizontal, does that matter?

 

[AnotherHiggins]

The key is that you said if you transpose your data, HLookup works. So if you don't transpose the data, VLookup will work.

What counts is how the data you are looking up is stored. You indicated that these are in columns, not rows. So Topic1, Topic2, etc. are stored vertically. Therefore you use a VLookup.

From Excel's help file on VLookup:
The arguments of VLookup are as follows:

VLOOKUP([blue]lookup_value[/blue],[red]table_array[/red],[green]col_index_num[/green],[purple]range_lookup[/purple])

[blue]Lookup_value[/blue] is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

[red]Table_array[/red] is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

[green]Col_index_num[/green] is the column number in table_array from which the matching value must be returned.

[purple]Range_lookup[/purple] is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
____

So the third argument,[green]col_index_num[/green], determines which column the data is returned from.

An example might look like this:

[tab]=VLOOKUP("Topic1",SheetName!A1100,4,False)
That would return the data found in column D on the same row where "Topic1" appears in column A. The first argument can also be replaced with a cell reference.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

If you have vertical lists that you want to return the data in the nth column, use vlookup

If you have horizontal data that you wish to return th enth row, use hlookup

Please post a copy of the formula you have tried that doesn't work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Isis5898]

The VLOOKUP would work if the numbers were at the top. I think my boss is trying to use vertical columns to return horizontal numbers. He doesn't want to transpose or move anything around. I tried that already for the HLOOKUP and it worked. He is just convinced that it should work his way.

Serenity Now!

 

[SkipVought]

Please post

1. a sample of the data in the lookup table

2. your formula

3. a detailed description of the FIRST VALUE you are looking up and what you expect to have returned.

Skip,

_[red][/red]

 

[Isis5898]

1.TOTAL TOPICS MISSED TOPIC 1 TOPIC 2
33
28
31
35
37
33
36
25 1
32 1
31
38 1
41 1 1
44
30
31 1
37 1
35
TOPICS MISSED TOTALS 2 5 <This is row 19>

2.The formula was the HLookup, returning the row # 19 field

3.
TOPIC 1
TOPIC 2
TOPIC 3
TOPIC 4
TOPIC 5
TOPIC 6
TOPIC 7
TOPIC 8
TOPIC 9
TOPIC 10

This is the lookup field, we are trying to match up Topic one with Topic one on the table sheet and return the row 19. I think the problem is that the #3 information is in a column and all the other data is vertical.

Please help!

 

[xlbo]

I think your issue is with absolute and relative references

Once again, please post the formula you have tried which doesn't work as despite 2 requests, you still have not posted the actual formula that doesn't work. Until we see what you have tried, we cannot suggest how you might need to modify it. From what you have shown, hlookup should work just fine

something like this should work (replace ranges with your own) but this is a stab in the dark because you havn't shown what is not working.......

=HLOOKUP($A2,Sheet2!$A$1:$Z$19,19,false)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Isis5898]

Sorry:

=HLOOKUP(Topics!A2,'AGE Data'!$Q$1:$DM$20,19)

 

[xlbo]

Ok - thank you.

Firstly, you have left out an argument - there are 4 arguments to the lookup formulae. At the moment, it is not searching for an exact match and may bring back data not associated with the data you were looking for.

Try this as a 1st step:

=HLOOKUP(Topics!A2,'AGE Data'!$Q$1:$DM$20,19,FALSE)

Given your data layout, that formula should work fine. Simply copy it down your list and there is no reason it shouldn't work. If not, can you give any further explanation to "I can't get it to work" ? Does it return anything or an error messaage ? If it returns something, what does it return ? If it returns an error message, which error ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Isis5898]

Thanks. That worked. I had tried that before, but another part of the formula must have been wrong then.

Thanks!