How to find a column number using variable criteria?
 

I'm trying to find a column value return similar to the function the MATCH
function performs for rows.

I have a large spreadsheet with part numbers for a product in row 1, column
headers under each part number and the 6 columns to the right and parts data
from rows ~3-~1500.

product-partnumber
1 2 3 4 5 6 7 (COLUMN headers)
A B C D E F G (data rows 3-1500)


I have dozens of part numbers for products which each have a parts list
under them so I need to be able to find the column the product-part number is
in, search down the column for the reference designator of the part and then
right 1 column in the same row for the part number of a failed component.

How to find a column number using variable criteria?
 

See Debra's great article at http://www.contextures.com/xlFunctions03.html
to get a solution

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"RobofMN" wrote:

I'm trying to find a column value return similar to the function the MATCH
function performs for rows.

I have a large spreadsheet with part numbers for a product in row 1, column
headers under each part number and the 6 columns to the right and parts data
from rows ~3-~1500.

product-partnumber
1 2 3 4 5 6 7 (COLUMN headers)
A B C D E F G (data rows 3-1500)


I have dozens of part numbers for products which each have a parts list
under them so I need to be able to find the column the product-part number is
in, search down the column for the reference designator of the part and then
right 1 column in the same row for the part number of a failed component.

How to find a column number using variable criteria?
 

That page had helpful data but none that could address my paticular problem
:) as far as I could tell. Finding out that match works on both rows &
columns was very useful and I saved the page in my favorites and rated the
reply helpful.

That page however is geared toward the use of one table while I have many
tables. This prevents me from knowing either the row or column the desired
data is in advance like is possible with having only 1 table. Index requires
knowing the row first and in my case I can determine with a formula the
column first but not the row unless the data is reorganized significantly. I
could name the tables but then I would need a custom formula for every
product part number and that is what I'm trying to get away from.

In the end I did reorganize the data from a series of table spreadout
horizontally into a series of tables vertically and inserting a column with a
value that combines the product part number with the reference designator of
the part to create a unique value and give the overall appearance of 1 large
table. This allowed the use of vlookup in the formula:
=IF(OR(AND(ISTEXT($N12050),NOT(ISBLANK($O12050))), NOT(ISBLANK($O12050))),VLOOKUP(TRIM($O12050),Data! $A$1:$D$621,2,FALSE),IF(AND(ISTEXT($N12050),ISBLAN K($O12050)),VLOOKUP(VLOOKUP($C12050&$N12050,dATA2! $A$1:$D$28810,4,FALSE),Data!$A$1:$D$621,2,FALSE)," No Ref_des"))

"Sheeloo" wrote:

See Debra's great article at http://www.contextures.com/xlFunctions03.html
to get a solution

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"RobofMN" wrote:

I'm trying to find a column value return similar to the function the MATCH
function performs for rows.

I have a large spreadsheet with part numbers for a product in row 1, column
headers under each part number and the 6 columns to the right and parts data
from rows ~3-~1500.

product-partnumber
1 2 3 4 5 6 7 (COLUMN headers)
A B C D E F G (data rows 3-1500)


I have dozens of part numbers for products which each have a parts list
under them so I need to be able to find the column the product-part number is
in, search down the column for the reference designator of the part and then
right 1 column in the same row for the part number of a failed component.