index and multiple match not working.

[trekker1218]

I have read most of the other threads and I have used index and match many times with multiple searches.
I am looking to insert cell data from a second sheet into sheet 1 but only if meets a double verification of matching cells.

I've been using this and getting an error.

=INDEX('orig-data'!Z:Z,MATCH(1,(U19548='orig-data'!O:O)*(AE19548='orig-data'!Y:Y),0))


I want to index a COL on (Orig-Data) sheet. But only show the result of the index if 2 matches are true.

 

[Fluff]

Did you confirm it with Ctrl Shift Enter, rather than just Enter?

 

[trekker1218]

Yes... same result. When i evaluate the formula. it finds the match of the ROW (#19548 on Orig-Data sheet)
and the data in that row.

 

[Fluff]

Yes... same result.

What result?

 

[trekker1218]

#n/a

 

[Fluff]

That suggests it can't find both values on the same row.

 

[trekker1218]

That suggests it can't find both values on the same row.

Orig-Data

New Sheet

I am trying to fill AF2 with the date from Orig-Data Z2 but only if G2 matches U2 and Y2 matches AE2

 

[Fluff]

That should work, although it's best to avoid using whole column references in array formulae.
Check that none of the values have leading/trailing spaces.

 

[trekker1218]

I only did the full columns for the chat here. I usually isolate the col and rows.
Is there a way to INDEX multiple columns with this formula.
Reason being. The Value Horsepower may appear in different columns with result in adjacent column.

In orig-Data sheet Columns y & Z represent the horsepower. Just noticed that further down my data sheet the Horsepower column is in AA & AB

 

[Fluff]

You're best bet would be to sort out the data so it's uniform, rather than trying to create a very messy formula to get round the problem.