Trouble with Complicated VLookup

[jgibbons89]

Hi Everyone,

I'm attempting to write a vlookup (or any variation) formula for the information attached. This excel spreadsheet contains a little over 12,000 rows.

I would like 'DOT Code' to be the lookup value and for all the information contained immediately to the right to populate. This can be accomplished with a vlookup for each individual value (although I'm sure there must be an easier way, if anyone can provide insight as to how I can write one formula for the entire row).

However, my main problem lies with the information just to the left of the DOT Code info. Besides being to the left of the lookup value, it's at the top of each section. How can I get each one of those DOT codes to populate a spreadsheet with its respective information.

For instance, if I were to type in "221.467-010" I want it to pull the string of information to the right and left of the call. The information contained to the right and within the same row, as I said, can be accomplished via a vlookup. But the information to the left is, not only to the left, but up 4 rows, e.g. 43-5111 | Weighers, Measurers | (4.0 to < 6.0) | 69,870 | 33.

To be clear, if I were to type in another DOT, such as '221.482-018', all the information to the left would be the same the same as the DOT code before this because they are in the same category, but the info to the right would change.

There must be some sort of nested Vlookup. I'm just having difficulty getting it to work.

Lastly, if there were some way to get all that to work, I'd like the numbers chart to the right to populate as well.

Any and all help would be very much appreciated! More than happy to provide further information. Thanks so much.

ExcelScreenshot.png

[Richard Buttrey]

Hi, and welcome to the forum.

Please attach the workbook, pictures are rarely much use - see guidance in the rules area.

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jgibbons89]

Alright, I attached the workbook with a before and after sheets.

[martindwilson]

doesnt look like a workbook ......... still seems to be an image

[jgibbons89]

Sorry about that! I'm not sure what happened.

[FDibbins]

This will work....
=INDEX('O-NET-SOC 2010 Occupation Listi'!$A:$AW,MATCH($H8,'O-NET-SOC 2010 Occupation Listi'!$H:$H,0),MATCH(A$6,'O-NET-SOC 2010 Occupation Listi'!$A$6:$AW$6,0))
However, in the columns where you have no entries for the DOT code, this will return 0

[jgibbons89]

Thanks so much for your help. I pasted that formula into cell A8 and got the correct result. However, I'm not sure where to go from here, as I'm not to familiar with match and index functions. Am I supposed to set up each individual cell with minor tweaks of that formula? Or is there a faster way to do the whole thing?

[FDibbins]

Just copy it down as-is, it should not need any adjustments

[jgibbons89]

Wow, that's pretty impressive. I definitely need to learn how those functions work. Seems to be way faster than any VLookup.

So the information to the right seems to work great. But, except for the top row, I'm getting all 0's for the information on the left.

[FDibbins]

INDEX/MATCH works pretty much the same as vlookup(), but vlookup() has the disadvantage of (generally) not being able to return values to the left of the criteria column (DOT column in your case)

So the information to the right seems to work great. But, except for the top row, I'm getting all 0's for the information on the left.

yes, I already commented onthat in post # 6

However, in the columns where you have no entries for the DOT code, this will return 0

In your file, most of the data in columns A:G is empty, so if you look at the 2nd entry on "after"....209.587-010
That can be found in row 1740, but A1740:G1740 is blank. I understand you were expecting to have the vvalue in A1733, but the value you are looking for is not in that row, is is (in this case) 7 rows up...in others it could be
33 or more rows up.

[jgibbons89]

Thanks for all your help! Do you have any ideas as to how to get this last part to work? Is there any way to get that data into those cells without ruining the visual aesthetics? Like is my only option to write those cells in white font?