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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the quick response and the two solutions!
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
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).
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).
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