Lookup some text based on three criterias.

Hi.

I'm new to the place, so I don't really know how this works.. But I have a work-related excel problem where I have to lookup some text from a cell, that's based on three criterias. I've tried using the sumproduct function, but it says I'm using the wrong type of data (I can only assume this means text).

Any suggestions to a function I can use to find the corresponding data using three text criterias?

//Peter

Hi,

Welcome to the Forum.

Can you please post a sample template of your workbook in excel format describing the different texts that you need to lookup?

Kopi af Udkast.xlsx

I've written an explanation in the excel to show you what I mean. Hopefully it makes sense.

//Peter

Hi,

SUMPRODUCT won't work here coz you have text not numbers in the last column (Sheet4, Column Q). If you change them to numbers and modify your formula as following, it works.

=SUMPRODUCT(--('Sheet 4'!A17:A28=H2),--('Sheet 4'!B17:B28=G2),--('Sheet 4'!C17:C28=I2),'Sheet 4'!Q17:Q28)



Alternatively, you can use the following array formula, this needs to be confirmed by pressing CTRL+SHIFT+ENTER

={INDEX('Sheet 4'!A17:Q28,MATCH(H2&G2&I2,'Sheet 4'!A17:A28&'Sheet 4'!B17:B28&'Sheet 4'!C17:C28,0),17)}

I have updated this in Sheet1 AN3

Wow, thanks a million. Really helpful!

You are welcome.

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved. Also, I would like to inform you that you can thank those who have helped you by clicking on the * which is at the bottom left corner of the respective post.