. 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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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 trialMichael_GK-Project_v2.xlsx

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:$

Column C

=VLOOKUP($A2,Sheet1!$C$1:$

Thanks

Rob

Column B:

=VLOOKUP($A2,Sheet1!$C:$AY

Column C:

=VLOOKUP($A2,Sheet1!$C:$AY

Alternative which is much shorter again using combined OFFSET statements.

Column B:

=OFFSET(Sheet1!$C$1,MATCH(

Column C:

=OFFSET(Sheet1!$C$1,MATCH(

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

Microsoft Office

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

For Alt_UOM, try below:

=IF(VLOOKUP($A2,Sheet1!$C:

For Alt_UOM_Conv, try below:

=IF(VLOOKUP($A2,Sheet1!$C:

Check in attached...

Michael_GK-Project.xlsx