Syntax for choose col with array match with another

[Ekac]

Hi,

I am using below formula but giving me value error.

Please Login or Register  to view this content.

Kindly can anyone rectify the code where it has gone wrong.

[HansDouwe]

This part of the formula [A3:A2198=LUX!C6:C1236] creates a array of 2196 (2198-3+1) rows and 1 column. The first 1231 elements are TRUE or FALSE, The rest is #N>A.

With this you can never filter a table with 1231 rows and 18 columns.

To prevent a value error you need for this table a filter with booleans that consists of 1231 rows (and 1 column) or consist of 18 columns (and 1 row).

[Ekac]

So,then how can I get 11th column of LUX sheet by matching existing sheet range A3:A2198 with that of LUX sheet range C6:C1236 and if any of existing range not found will be zero.

What would be the expected formula in this situation.

[HansDouwe]

Please upload a small sample sheet and show us what you want (with manually added expected results).

[Ekac]

Please upload a small sample sheet and show us what you want (with manually added expected results).

Ok,let me give some time.

[HansDouwe]

almost like is not enough.

To help you further, I need an example that fits the formula you tried, but with a maximum of 20 rows including manually indicating the expected results.

[Ekac]

I have tried with another function index and match function,

Please Login or Register  to view this content.

This seems to work but why not above #Post 1 function works.

[HansDouwe]

Nice you found a solution by your own .

but why not above #Post 1 function works.

I explained that in detail in post #2.

To prevent a value error you need for this table a filter with booleans that consists of 1231 rows (and 1 column) or consist of 18 columns (and 1 row),but A3:A2198=LUX!C6:C1236 returns a filter that exists of 1231 booleans and 865 cells with#N/A. Because that does not match FILTER returns in that case #VALUE.

[Ekac]

Is there is an alternative to above index match formula in O365.

[HansDouwe]

This formula does exactly the same thing as your formula in Post #7:

Please Login or Register  to view this content.

If you want to omit the rows with 0, try

Please Login or Register  to view this content.

[Ekac]

Can u use Choosecols function,I think I like this function to use.

[HansDouwe]

For example:

Please Login or Register  to view this content.

[Ekac]

Hi,

I have applied your formula at H3 in master sheet. But it is pulling data wrong.I have mentioned my result at I column.From Lux Sheet,K column data is to be pulled by matching C column.

with that of A column of master sheet.Hence,looking for Choose Cols function.

I have attached the file for your kind reference.

[AliGW]

Try this:

Please Login or Register  to view this content.

[Ekac]

As per sample demo it is pulling correct data but when used in my real workbook it gives some wrong results.Is there any other similar formula that can be tested.

[AliGW]

You need to provide sample data to show it FAILING. We cannot amend it or suggest alternatives without seeing realistic data.