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

 

[crburke92]

Spelling was my first check. 100% on spelling being correct. Even on the check you got me to do the spelling is absolutely the same

 

[Fluff]

Is the range on the datadump sheet?

 

[crburke92]

Yes.

So on the DataDump page:
Row one is the header in my HLookup (things like ‘sampledate’ ‘serialnum’ ‘h2’)
Row to is my column reference number that it should return for the Vlookup.

My table array for the HLookup is A1:B94

I have the formula written on a different worksheet but part of the same workbook.

 

[Fluff]

Is sampledate a named range or just a column header?

 

[crburke92]

It’s just a column header.

 

[Fluff]

In that case it should be something like
=VLOOKUP($F$13,DataDump!$A$3:$CQ$8,HLOOKUP("sampledate",DataDump!$A$1:$Z$2,2,0),0)
where the part in red is the range containing your headers & the column number below

 

[crburke92]

Ah that clears the issue. Makes sense, I never really thought about not needing to reference the page for what you’re actually looking up. Thanks Fluff you’re the best!

 

[Fluff]

You're welcome & thanks for the feedback