Using a Vlookup function without having to transpose my data

Originally Posted by XOR LX

Could you possibly attach one illustrating your problem?

AND your desired results..

Yeah, I made an attachment.

I want the values of the VLookup translation to go into the yellow boxes

Try

=VLOOKUP(INDEX($B:$B,(COLUMNS($A1:A1)-1)+3),$D$3:$E$7,2,)

Do Not Change the $A1:A1 referece, it's not related to your data.

Thanks, Jonmo1

Could you show it in an attachment? I cant get it running in mine

You already have the attachment.

With the file you posted, put the formual I posted in H3 and fill right.

Thanks, that did it!

Great that you guys were patient with, i'm kind of a beginner.

But what do the -1 and +3 do? I can't figure out their role

This COLUMNS($A1:A1) returns the # of columns in the reference (1)
It will incriment as dragged right, it changes to
COLUMNS($A1:B1) = 2
COLUMNS($A1:C1) = 3
etc.

The -1 turns COLUMNS($A1:A1)-1 into 0
The +3 makes it begin in Row#3

Yes, this would work as well
COLUMNS($A1:A1)+2

But I like to turn it back to 0 and add the beginning row #. Seems to make it easier to manage.

Excel Transposition and Sum via Formulas.xlsxThis seems to be very similar to what I am trying to do, except that I need to match against 2 criteria. i.e. Dates are displayed in 1 column, Departments are displayed in 1 row multiple columns and sometimes duplicated, and values are in each row below the Department row. I want to transpose the data so I have 1 column for the Dates, 1 column for the Departments, and a 3rd column to sum the values. I attached an example of what I'm trying to accomplish. Any ideas for what formula to use to sum the values that match the 2 criteria for Date and Department?