VLookup and HLookup

[crburke92]

Getting a "There's a problem with your formula" error.

So what I have is... When I get results back from a lab, they also supply a .csv file. All data points I want to reference are left to right, while each sample is top to bottom. Each sample has a specific serial number I want to use as my point of reference for my VLookup. I'm trying to imbed an HLookup but it's giving me issues. The column headers, while always the same text wise, are not always in the same column reference file to file (sometimes its 70 columns long, others are 95). My hope was to insert new B Row with values 1-100 in them, VLookup the serial number, and hlookup the column header (which is text).

=VLOOKUP($F$13,DataDump!$A$3:$CQ$8,HLOOKUP(DataDump!"sampledate",DataDump!$A$1:$B$94,2,),)

$F$13 is where I type in the serial number.
DataDump is the sheet I copy the .csv data in to.


Prior to trying this I had a working formula of:

=VLOOKUP($F$13,DataDump!$A$3:$CQ$8,18,)

Where 18 was the literal column number the data I was trying to reference is located. However as I said above, that column number can change, and I'm stuck going through 20 of these formulas and finding the column reference for this specific .csv file.

Any help is much appreciated

 

[Fluff]

Try removing the " from the named range

 

[crburke92]

I tried with nothing, as well as ‘

Neither of those seemed to work but I do get two different errors depending on what I use. I evaluated the formula and it is stemming from the ‘sampledate’ portion.

 

[Fluff]

Do you have a single cell called sampledate on the DataDump sheet?

 

[crburke92]

Yes that’s correct. There’s only the one cell named that on the page.

 

[Fluff]

Change the HLOOKUP to VLOOKUP

 

[crburke92]

Still no go.

When I put nothing around sample date I get NAME?

When I put ‘ or “ I get a problem with the formula.

If I reference the actual cell sample date is in (DataDump!$A$6) it returns the value I want..

It seems the issue is how I’m referencing a text value but everything I’ve read says I’m doing it right?

 

[Fluff]

On the datadump sheet enter this in an unused cell
=sampledate
what does it return?

 

[crburke92]

Ah **** I didn’t think of formatting.

It =sampledate returned NAME?...

What would be the proper formatting?

 

[Fluff]

It's nothing to do with formats, if you are getting the NAME error then you don't have a named range called "sampledate", check the spelling.