CODEWITHSUNDEEP
vbaexcelvlookup
Josiah Cowden
Josiah Cowden

Reputation: 49

Excel =vlookup or macro with variables

i have 4 identical spreadsheets with same headers that I need to funnel into one sheet, some of the data is duplicated and i would like to make a formula that I can copy into the cells that checks each column for data.

So this is all the data. !http://i1372.photobucket.com/albums/ag321/josiahcowden/example_zps6f0c2e2c.png

and this is the header info of each !http://i1372.photobucket.com/albums/ag321/josiahcowden/headerdata_zps299e9332.png

i need the formula to look like =vlookup(A2, Sheet1!A:AM, 16, false) but with an IF statment to look at col index num 22 (Y) if no data is in 16 (P), and then go to 28 (AB) if there is no data in 22 (Y) and lastly for the fourth sheet 34 (AH) if there is no data in 28 (AB).

Upvotes: 1

Views: 543

Answers (1)

guitarthrower
guitarthrower

Reputation: 5834

not VBA but this formula will do it

=IF(VLOOKUP(A2,Sheet1!A:AM,16,FALSE)<>"",
    VLOOKUP(A2,Sheet1!A:AM,16,FALSE),
    IF(VLOOKUP(A2,Sheet1!A:AM,22,FALSE)<>"",
       VLOOKUP(A2,Sheet1!A:AM,22,FALSE),
       IF(VLOOKUP(A2,Sheet1!A:AM,28,FALSE)<>"",
          VLOOKUP(A2,Sheet1!A:AM,28,FALSE),
          VLOOKUP(A2,Sheet1!A:AM,34,FALSE)
       )
    )
 )

Upvotes: 2

Related Questions