Hlookup/Vlookup help-this should be easy but I just can't figure it out

sycodiz

New Member
I have a very simple excel sheet. I am going to break out sections and give them to different team members. No team members will have the same data.
I need to match on Vendor number (Rows) and dates (columns) as shown below.
The main sheet will be pulling the data from each of the other sheets. (I can change the name of each sheet later)
To simplify this, let's say I was only pulling the data from one sheet.
Below is what the main sheet looks like.
The sheet I need to pull the data from will have the same dates, but the vendor numbers most likely will not be on the same row. Also there is a chance the dates are not in the same columns.
What formula can I write in B3 (and then copy over and down) that would return this information?
Any help is greatly appreciated.

Example
 A B C D E F G H I J VENDOR 2/1/20 2/2/20 2/3/20 2/4/20 2/5/20 2/6/20 2/7/20 2/8/20 2/9/20 12345 9876

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Possibly you need an INDEX and MATCH combination,
The sheet I need to pull the data from will have the same dates, but the vendor numbers most likely will not be on the same row. Also there is a chance the dates are not in the same columns.
We would need to see an example of that as well, there are too many if's but's and maybe's to be able to suggest anything useful based on what we have so far.

sycodiz

New Member
1st post has the sheet I need these numbers pulled from... as you can see the dates do not line up with the other sheets columns and the vendor numbers are not in the same order.

 C D E F G H I J K L VENDOR 2/1/20 2/2/20 2/3/20 2/4/20 2/5/20 2/6/20 2/7/20 2/8/20 2/9/20 9876 10 50 16 18 32 0 0 2 4 12345 1 0 2 1 3 4 0 2 1

jasonb75

Well-known Member
That should work with a basic INDEX MATCH formula, when I read your first post, I had visualised a completely different layout. In B3, then drag and fill as needed.
Sheet2 refers to the source in post 3.

=INDEX(Sheet2!\$D\$2:\$L\$3,MATCH(\$A2,Sheet2!\$C\$2:\$C\$3,0),MATCH(B\$1,Sheet2!\$D\$1:\$L\$1,0))

sycodiz

New Member

Thanks for your help but that did not work. I may not be explaining well enough. The columns are only examples. These columns could change. And I apologize, the Vendor would always be in column A, the dates could be in different columns. I want to match the vendor and the date and return the value. It really should be easy. I am banging my head here trying to figure this out.

jasonb75

Well-known Member
These columns could change.
As long as the dates are always in the same row it should still work, you just need to expand the ranges to suit.

Breaking the formula down for you, the first range is the table of values to return, unlike vlookup this doesn't include the column with the vendor numbers (or the date row of hlookup).
The second range is the vendor number that you want to look for, the third range is the column of vendor numbers to search.
The fourth and fifth ranges apply to the dates in the same way.

=INDEX(Sheet2!\$D\$2:\$L\$3,MATCH(\$A2,Sheet2!\$C\$2:\$C\$3,0),MATCH(B\$1,Sheet2!\$D\$1:\$L\$1,0))

The first MATCH should always be the vlookup (find the correct row), this should have the same number of rows as the INDEX range, but only 1 column.
The second MATCH should be the hlookup (find the correct column), this should have the same number of columns as the INDEX range, but only 1 row.

sycodiz

New Member
I figured it out.
=INDEX(Sheet2!\$B\$3:\$I\$7,MATCH(\$A3,Sheet2!\$A\$3:\$A\$8,0),MATCH(Sheet1!B\$1,Sheet2!\$B1:\$I1,0))
Thanks again. What you gave me did help!

Replies
4
Views
52
Replies
5
Views
95
Replies
2
Views
311
Replies
1
Views
83
Replies
2
Views
200