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

sycodiz

New Member
Joined
Jun 15, 2008
Messages
18
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
ABCDEFGHIJ
VENDOR2/1/202/2/202/3/202/4/202/5/202/6/202/7/202/8/202/9/20
12345
9876
 

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 15, 2008
Messages
18
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.

CDEFGHIJKL
VENDOR2/1/202/2/202/3/202/4/202/5/202/6/202/7/202/8/202/9/20
987610501618320024
12345102134021
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 15, 2008
Messages
18

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 15, 2008
Messages
18
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,055
Messages
5,575,840
Members
412,689
Latest member
amazonsellerassociation
Top