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

[rochytall2000]

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

 

[Fluff]

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]

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)

Id	Action	user	User_Email	Date and Time	UUID
123456789​	DOWNLOAD	user_name_is_bob	any@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]

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]

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]

Ok, try in C4 of sheet1 copied down

=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]

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.

 

[Fluff]

It looks as though you have two blank rows between each set of data, if that's the case use

=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)

 

[rochytall2000]

Thank you thank you thank you!!

You have saved me hours

=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]

You're welcome & thanks for the feedback.