excel - VBA complex vlookup -

okay saw number of questions similar mine none quite exact answers didn't help. although may not vlookup process similar 1 closest thing think of title.

i have column (a) of values in document , want excel go through each value , in document b find same value in column (a). each value finds want 3 things:

1. i want grab value 2 cells right of each matched value found in document b , paste 1 cell right of value in column of document a

2. i want @ value 3 right of each matched value found in document b , if value "time" put letter "t" 2 cells right of value in column of document a.

3. if value 4 cells right of each matched value found in document b > 5 want excel fill cell 3 cells right of value in column of document formula = value of (column g / column h) in document b same row.

in case wording confusing want more complex vlookup. document has column , want @ column fill in columns b, c, , d using table in document b

please let me know if have questions , in advance can give me.

each of things want starts out simple "vlookup", have twist. simplify things, should name ranges "a" , "b". selecting cells belong "a", typing name in address box (top left of screen). let's call "rangea". similarly, select cells belonging region b (first column through eighth column; make sure not include headers), , call rangeb. can begin:

"for each value in a, find corresponding value in b, , return value 2 right":

this simple vlookup. enter formula in cell right of (b1, probably):

=vlookup(a1, rangeb, 3, false) 

explanation: value in cell a1 in range called rangeb. find exact match (that's "false"). return corresponding value in column 3 (two right). drag formula way down (shortcut: double click little square in bottom right corner of cell when it's selected).

i want @ value 3 right of each matched value found in document b , if value "time" put letter "t" 2 cells right of value in column of document a.

this vlookup followed if. put following formula 2 cells right of (c1, probably) , drag down before:

=if(vlookup(a1, rangeb, 4, false) = "time", "t", "") 

explanation: before (but column 4, i.e. "three right"). test value found. if it's "time", enter "t" in cell; otherwise, enter "" (i.e. nothing).

if value 4 cells right of each matched value found in document b > 5 want excel fill cell 3 cells right of value in column of document formula = value of (column g / column h) in document b same row.

find match, take ratio. faster lookup once match function (in hidden column on sheet) have reference row number (we need 3 times), it's not necessary. unless sheet has thousands of rows not notice performance difference. according this thread there's no "quick way" achieve inside formula.

=if(vlookup(a1, rangeb, 5, false) > 5, vlookup(a1, rangeb, 7, false)/vlookup(a1, rangeb, 8, false), "") 

and there have it. note index(match... (the preferred method of @user2140261) can achieve same thing vlookup involves 2 functions. if decide create hidden column match function in it, can use index functions achieve other things - , efficient since use 1 match (as opposed recomputing 1 or more times each column).

i hope can figure out here.