HLookup Issue

[spenstar]

Hi All,

I have an issue where my HLOOKUP lookup_value is pointing to another cell that is also using a HLOOKUP ... it returns a #N/A but if I remove the HLOOKUP from the lookup_value cell and replace it with a value, it works.

Here's my formula that won't work: =HLOOKUP(G17,D32:K38,7,FALSE)
Here's what is in G17: =HLOOKUP(G16,'MSW Production'!C17:J21,2,FALSE)

I have tried putting a *1 at the end to see if it was a translation of text to numeric, but it didn't do anything.

Any help is appreciated.

 

[Joe4]

Welcome to the Board!

Are you sure that all the values you are matching on are of the same data type?
You can only compare "numbers to numbers" and "text to numbers".
Comparing "numbers entered as text to numbers" will not work.

You can easily check each one with the ISNUMBER function, i.e.
=ISNUMBER(G16)
=ISNUMBER(G17)
and then check the cells in D32:K38 and 'MSW Production'!C17:J21 that you think contain the matching value.

 

[spenstar]

Hi Joe4, thanks for the welcome! Yes, I tried that and they all come back as TRUE.

 

[spenstar]

Welcome to the Board!

Are you sure that all the values you are matching on are of the same data type?
You can only compare "numbers to numbers" and "text to numbers".
Comparing "numbers entered as text to numbers" will not work.

You can easily check each one with the ISNUMBER function, i.e.
=ISNUMBER(G16)
=ISNUMBER(G17)
and then check the cells in D32:K38 and 'MSW Production'!C17:J21 that you think contain the matching value.

Hi Joe4, thanks for the welcome! Yes, I tried that and they all come back as TRUE.

 

[Joe4]

Are you sure everything really matches exactly? No extra spaces or characters?

It might be helpful to post a sample of your data, in all the different places for us to see.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.