Multiple column search in Excel

. I need to look up a number in column C, and then lookup which of the following columns - P, W, AD, AK, AR and AY has the number 1, and based on that, I need Alt_UOM and Alt_UOM_Conv.
For Ex:
Let’s say I am looking up 10100 in Column C, Column P has 1, and I need the Alt_UOM from column J.
If I was to look up 10114, Column W has 1, and I need the Alt_UOM from Column Q.
If I use Vlookup,
=Vlookup(A2,’LUM IC11 UOM’ C:AZ, Col_Index, False)

 (NOTE – A2 is in sheet1 and the rest of the data is in sheet name – LUM IC11 UOM)

The relationship is
The Vlookup number(10100) is in Cell A2 and the result needs to be in B2 (Alt_UOM), C2 (Alt_UOM_Conv)
For the relationship that establishes the connections,
Column P – Column J
W – R
AD – X
AK – AE
AR – AL
AY - AS

If 1 is found in the BUY_FL_# column, then return the value from a column that is seven columns back.

Is there a better way to do this then to write a nested IF statement?
GK-Project.xlsx
Michael PaxtonProcess EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Micheal,

For Alt_UOM, try below:
=IF(VLOOKUP($A2,Sheet1!$C:$AY,14,0)="1",INDEX(Sheet1!$J:$J,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,21,0)="1",INDEX(Sheet1!$Q:$Q,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,28,0)="1",INDEX(Sheet1!$X:$X,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,35,0)="1",INDEX(Sheet1!$AE:$AE,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,42,0)="1",INDEX(Sheet1!$AL:$AL,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,49,0)="1",INDEX(Sheet1!$AS:$AS,MATCH($A2,Sheet1!$C:$C,0)),""))))))
For Alt_UOM_Conv, try below:
=IF(VLOOKUP($A2,Sheet1!$C:$AY,14,0)="1",INDEX(Sheet1!$K:$K,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,21,0)="1",INDEX(Sheet1!$R:$R,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,28,0)="1",INDEX(Sheet1!$Y:$Y,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,35,0)="1",INDEX(Sheet1!$AF:$AF,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,42,0)="1",INDEX(Sheet1!$AM:$AM,MATCH($A2,Sheet1!$C:$C,0)),IF(VLOOKUP($A2,Sheet1!$C:$AY,49,0)="1",INDEX(Sheet1!$AT:$AT,MATCH($A2,Sheet1!$C:$C,0)),""))))))
Check in attached...
Michael_GK-Project.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Commented:
Alternatively, you can create a named range for both the above formula, check in attached...
Michael_GK-Project_v2.xlsx
0
Rob HensonFinance AnalystCommented:
Are you OK with a helper column?

If so, in column BA of data sheet use this formula:

=17-MATCH("1",P2:AY2,0)  for row 2 and copied down as needed.

Then in Output sheet, column B
  =VLOOKUP($A2,Sheet1!$C$1:$BA$30,VLOOKUP($A2,Sheet1!$C$1:$BA$30,51,FALSE)-8,FALSE)

Column C
=VLOOKUP($A2,Sheet1!$C$1:$BA$30,VLOOKUP($A2,Sheet1!$C$1:$BA$30,51,FALSE)-7,FALSE)

Thanks
Rob
0
Michael PaxtonProcess EngineerAuthor Commented:
Thanks for the quick response and the two solutions!
1
Rob HensonFinance AnalystCommented:
Couple more options for you to consider; first one using same principle as Shums' first suggestion but shorter by nesting the IF statements within the VLOOKUP.
Column B:
=VLOOKUP($A2,Sheet1!$C:$AY,IF(INDEX(Sheet1!$P:$P,MATCH($A2,Sheet1!$C:$C,0))="1",8,IF(INDEX(Sheet1!$W:$W,MATCH($A2,Sheet1!$C:$C,0))="1",15,IF(INDEX(Sheet1!$AD:$AD,MATCH($A2,Sheet1!$C:$C,0))="1",22,IF(INDEX(Sheet1!$AK:$AK,MATCH($A2,Sheet1!$C:$C,0))="1",29,IF(INDEX(Sheet1!$AR:$AR,MATCH($A2,Sheet1!$C:$C,0))="1",36,IF(INDEX(Sheet1!$AY:$AY,MATCH($A2,Sheet1!$C:$C,0))="1",43,0)))))),FALSE)

Column C:
=VLOOKUP($A2,Sheet1!$C:$AY,IF(INDEX(Sheet1!$P:$P,MATCH($A2,Sheet1!$C:$C,0))="1",8,IF(INDEX(Sheet1!$W:$W,MATCH($A2,Sheet1!$C:$C,0))="1",15,IF(INDEX(Sheet1!$AD:$AD,MATCH($A2,Sheet1!$C:$C,0))="1",22,IF(INDEX(Sheet1!$AK:$AK,MATCH($A2,Sheet1!$C:$C,0))="1",29,IF(INDEX(Sheet1!$AR:$AR,MATCH($A2,Sheet1!$C:$C,0))="1",36,IF(INDEX(Sheet1!$AY:$AY,MATCH($A2,Sheet1!$C:$C,0))="1",43,0))))))+1,FALSE)

Alternative which is much shorter again using combined OFFSET statements.
Column B:
=OFFSET(Sheet1!$C$1,MATCH($A2,Sheet1!$C:$C,0)-1,6+MATCH("1",OFFSET(Sheet1!$P$1,MATCH($A2,Sheet1!$C:$C,0)-1,0,1,36),0),1,1)

Column C:
=OFFSET(Sheet1!$C$1,MATCH($A2,Sheet1!$C:$C,0)-1,7+MATCH("1",OFFSET(Sheet1!$P$1,MATCH($A2,Sheet1!$C:$C,0)-1,0,1,36),0),1,1)

OFFSET creates a range. If the OFFSET range is only one cell then it will return the value of that one cell. If it is a multiple cell range then the OFFSET needs a function to apply to that range to evaluate a result from the range.

Syntax of OFFSET is:
=OFFSET(Reference Point, Rows, Columns, Height, Width)

So in first OFFSET it creates a range that is only one cell (height 1 and width 1). It uses C1 as its reference point; the rows by which to offset from that point is determined by matching the Item number in column C (less 1 to allow for header); the columns is then determined using another OFFSET function which will create a multi cell range; starting at P1 it again uses the Item to determine the row offset (less 1 for header); 0 for column offset so range starts in column P, height is 1 and width is 36. This will create a range from P to AY on the row in which it matches the item number. The MATCH function will then find "1" within that range; column P will be 1, column W will be 8 etc. This value then has 6 added to it to allow for the columns between C & J. The MATCH result + 6 is then the offset from column C in the first OFFSET; offset 7 from C is J, offset 15 is Q, etc.

For column B it is the same but adding 7 to the MATCH result so that it is one further column.

Hope that all makes sense (1:00am in the UK so maybe a little confusing).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.