Excel: Lookup Value in Table and Return Data Depending on Result

Question

I've been pondering over this for quite a while, wondering if anyone can help?

So I'm attempting to look up an asset number in another table on a different sheet to determine the value of a cell.

1. Asset Data Example

Column1 Column2 Column3 Column4 Column5

Asset1 - Asset5 - Asset8 - Asset10 - Asset12

Asset2 - Asset6 - Asset9 - Asset11

Asset3 - Asset7 -

Asset4 -

Each column of data is specific to a result, so assets numbers in Column1 are "Successful", Column2 "In Progress" etc.

On another sheet, I have more detailed information for each asset and I want to reference the data above to determine the string value of a cell.

2.Example:

Asset - Model - Observation

Asset1 -8402 - Success

I want to find the asset in the table above (1) and then depending on which column it is in, insert one of five values into the cell under "Observation". So as "Asset1" is in the first column, this would be "Success."

I've tried using vlookup to find the asset and then using an If statement enter the obervation value but this doesn't work for each column. Is it possible to name the column and then return the column name depending on what column the asset is in?

Any help greatly appreciated.

Pavel_V Pavel_V · Accepted Answer · 2018-04-04T12:19:44

Since you tagged it excel 2016 I would assume you have the new functions there so in this case you could use IFS

=IFS(COUNTIF(AssetData!A:A;Sheet2!A2)=1;"Successful";
     COUNTIF(AssetData!B:B;Sheet2!A2)=1;"In progress";
     COUNTIF(AssetData!C:C;Sheet2!A2)=1;"Status3";
     COUNTIF(AssetData!D:D;Sheet2!A2)=1;"Status4";
     COUNTIF(AssetData!E:E;Sheet2!A2)=1;"Status5")

Where your first table in in sheet AssetDAta, columns A-D and your 2.Example in Sheet 2, columns A-C. This all assumes you have the asset in the data table exactly once. Of course there are more elaborate ways to do that like

=AGGREGATE(15;6;COLUMN(AssetData!A:E)/(AssetData!A:E=Sheet2!A2);1)

which would give you column number without multiple countif functions, but that might be too time and resource consuming.

Excel: Lookup Value in Table and Return Data Depending on Result

1 Answers