VLookup and HLookup

crburke92

New Member
Joined
Feb 5, 2019
Messages
47
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

New Member
Joined
Feb 5, 2019
Messages
47
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
Is the range on the datadump sheet?
 

crburke92

New Member
Joined
Feb 5, 2019
Messages
47
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
Is sampledate a named range or just a column header?
 

crburke92

New Member
Joined
Feb 5, 2019
Messages
47

ADVERTISEMENT

It’s just a column header.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2019
Messages
47
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,144,612
Messages
5,725,315
Members
422,610
Latest member
sanantonio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top