If Statements w/vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-1629611%22%20slang%3D%22en-US%22%3EIf%20Statements%20w%2Fvlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1629611%22%20slang%3D%22en-US%22%3E%3CP%3EOther%20excel%20lovers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20string%20some%20IF%20statements%20along%2C%20using%20vlookups%20as%20well.%20I%20want%20to%20select%20value%20from%20the%20audits%20list%20to%20perform%20a%20vlookup%2C%20pulling%20the%20data%20from%20the%20table%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20I%20select%20%22Pallet%20Heights%22%20from%20the%20audit%20list%2C%20it%20looks%20at%20the%20location%20in%20A13%2C%20finds%20that%20in%20the%20table%20below%2C%20and%20returns%20the%20correct%20column%20in%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20simple%20and%20I%20can%20get%20it%20to%20work%20up%20until%20the%20third%20IF%20statement%20when%20it%20all%20starts%20unraveling.%20I%20just%20can't%20seem%20to%20understand%20how%20the%20logic%20works%20when%20you%20want%20to%20do%20three%20or%20more%20IF%20statements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1629611%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1629675%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Statements%20w%2Fvlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1629675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780177%22%20target%3D%22_blank%22%3E%40Koonies%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20have%20some%20positions%20to%20trace%2Fchoose%20I%20would%20suggest%20another%20simple%20formula.%3C%2FP%3E%3CP%3EOFFSET(Reference%2CRowPosition%2CColumnPosition)%3C%2FP%3E%3CP%3ETo%20get%20these%20positions%20(Row%20and%20Col)%20you%20can%20use%20the%20formula%20Match()%3C%2FP%3E%3CP%3ETranslating%20for%20your%20file%20needs%20the%20following%20formula%20will%20return%20your%20desirable%20result%3A%3C%2FP%3E%3CP%3E%3DOFFSET(play%5B%5B%23Headers%5D%2C%5BLocation%5D%5D%2CMATCH(A13%2Cplay%5BLocation%5D%2C0)%2CMATCH(F4%2Cplay%5B%23Headers%5D%2C0)-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPs.%3A%20All%20your%20columns%20name%20must%20to%20be%20equal%20what%20you%20are%20looking%20for.%20No%20abbreviations%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Other excel lovers,

 

I'm trying to string some IF statements along, using vlookups as well. I want to select value from the audits list to perform a vlookup, pulling the data from the table below.

 

So if I select "Pallet Heights" from the audit list, it looks at the location in A13, finds that in the table below, and returns the correct column in the table.

 

Seems simple and I can get it to work up until the third IF statement when it all starts unraveling. I just can't seem to understand how the logic works when you want to do three or more IF statements.

 

Any help is appreciated!

3 Replies
Highlighted

@Koonies 

As you have some positions to trace/choose I would suggest another simple formula.

OFFSET(Reference,RowPosition,ColumnPosition)

To get these positions (Row and Col) you can use the formula Match()

Translating for your file needs the following formula will return your desirable result:

=OFFSET(play[[#Headers],[Location]],MATCH(A13,play[Location],0),MATCH(F4,play[#Headers],0)-1)

All your columns name must to be equal what you are looking for. No abbreviations 

If you want to use IF statement, the following formula will work as well

=VLOOKUP(A13,play,IFS(C4=F4,D4,C5=F4,D5,C6=F4,D6,C7=F4,D7,C8=F4,D8,C9=F4,D9),0)

Or perhaps some elegant alternative

=VLOOKUP(A13,play,SUMPRODUCT((C4:C9=F4)*(D4:D9)),0)

 

Now you have some other options to choose

Highlighted

@Juliano-Petrukio 

 

Thanks for your help! That worked wonderfully.

Highlighted

@Koonies 

You're welcome, By the way don't be shy on hit the like button and don't forget to mark as Official/Best Answer to help the other members find it too.