VLOOKUP across two sheets, first sheet has data vertically, second horizontally

rochytall2000

New Member
Joined
Sep 15, 2006
Messages
24
Office Version
  1. 365
  2. 2019
HI all, it's been a while since I've posted.

I'm trying to do a "VLOOKUP" of sorts across two tables in different sheets

I want to compare value in B4 on Sheet 1 (data listed vertically) against an array on Sheet 2 ( (A4 is start of array data listed horizontally, each row is a unique ID with various columns of data)
Sheet 1 (B4:B7121)
Sheet 2 (A4:F893)


What I am struggling with is creating a formula (or combining formula) so that I can drag it down in sheet one, and as I go, B4 changes to B5 and the "VLOOKUP" column changes to Column 2.

I've tried using COLUMN() but I don't think I'm using it correctly within my VLOOKUP

I've also looked at combing INDEX and MATCH but again I think I'm way off

Any and all help greatly appreciated, it is probably something simple I am missing

Thanks Duncan
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Are you trying to lookup B4 in sheet2 col A & return the value from col B, then lookup B5 in sheet2 col A & return the value from col C?
 

rochytall2000

New Member
Joined
Sep 15, 2006
Messages
24
Office Version
  1. 365
  2. 2019
Hi Fluff, thanks for taking the time to read my post

Are you trying to lookup B4 in sheet2 col A & return the value from col B, then lookup B5 in sheet2 col A & return the value from col C?

It would be similar to this `lookup B4 in sheet2 col A & return the value from col A, then lookup B5 in sheet2 col B & return the value from col B`

If I was doing basic vlookup it would be =VLOOKUP(B4,$A$4:$F$893,1,FALSE) then I'd manually change the column number as I usually drag it horizontally.

Sheet1 (Column B)
123456789 (row 1)​
DOWNLOAD
user_name_is_bob
any@email.com
2020-06-01T09:41:46
987654321(row 6)​
{blank}
{blank}

Sheet2 (starting at Column A)
IdActionuserUser_EmailDate and TimeUUID
123456789​
DOWNLOADuser_name_is_bobany@email.com
06/01/2020 09:41:46​
987654321​

I'm trying my hardest to avoid having to do this, I have nearly 2000 rows in Sheet 1, the data structure repeats as above (it comes to me as JSON, I then do a Text to Columns. I'm verifying what is in the JSON matches what is in the original spreadsheet

VLOOKUP(B4,$A$4:$F$893,1,FALSE) = gives 123456789
VLOOKUP(B5,$A$4:$F$893,2,FALSE) = gives DOWNLOAD

I tried HLOOKUP but that went horribly wrong

There may be a better way, or tool out there that can be help.

Either way I need help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Not sure I fully understand, wouldn't you need to lookup the ID each time? Otherwise you are just looking to see if a value exists anywhere in a column.
So wouldn't you need
VLOOKUP(B4,$A$4:$F$893,1,FALSE)
VLOOKUP(B4,$A$4:$F$893,2,FALSE)
VLOOKUP(B4,$A$4:$F$893,3,FALSE)
 

rochytall2000

New Member
Joined
Sep 15, 2006
Messages
24
Office Version
  1. 365
  2. 2019

ADVERTISEMENT

Apologies, you are correct my example should have been:

VLOOKUP(B4,$A$4:$F$893,1,FALSE) = gives 123456789
VLOOKUP(B4,$A$4:$F$893,2,FALSE) = gives DOWNLOAD
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Ok, try in C4 of sheet1 copied down
Excel Formula:
=VLOOKUP(INDEX($B$4:$B$99,INT((ROWS(B$4:B4)-1)/6)*6+1),Sheet2!$A$4:$F$893,MOD(ROWS(B$4:B4)-1,6)+1,0)
 

rochytall2000

New Member
Joined
Sep 15, 2006
Messages
24
Office Version
  1. 365
  2. 2019

ADVERTISEMENT

Thanks, I would never have come up with that!! I and autofill and for some reason it isn't quite working as expected. Where I have put the word Entries is where there are values to be validated. I've obfuscated some details as you can see at the bottom, this pattern repeats all the way down, success on one then two "blocks" fail, then it works.

I haven't tried changing the formula as I don't fully understand it and will just make things much worse. So I can try and resole it myself where should I be looking within that formula.


1602857830337.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
It looks as though you have two blank rows between each set of data, if that's the case use
Excel Formula:
=VLOOKUP(INDEX($B$4:$B$101,INT((ROWS(B$4:B4)-1)/8)*8+1),Sheet2!$A$4:$F$893,MOD(ROWS(B$4:B4)-1,8)+1,0)
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,749
Members
412,339
Latest member
sstackho
Top