How to find a result when you need to search a number of columns

I have a problem that I am sure a formula will fix for me but I just cant seem to work it out, I need to search multiple columns to find a match before I want the result selected which itself will be in a different column.

It seems to me to be an extension of the VLookup function. Attached is an example of the sort of spread sheet I am trying to pull data from. I am trying to get the result of Area 5G, for site ZZ, KPI 2.1, Date Apr-13. As well as the result for Area 5G, Site (empty), KPI 2.1, Date May-13. I hope this is not to convoluted and someone can assist.

Thanks

Try Pivot table

Which column do you want to get result? Is that Column F? and with criteria Area, KPI, and Month?

azumi yes I'm looking to get the result from column F in sheet 1, with the criteria searched 'Area' or 'Site'(both of these will be on the same drop down menu), this search will then populate against the 'KPI' and 'Month'.

Hope it helps, please see the file

That helps a lot azumi, I have never used INDEX and MATCH, how about if the drop down menu at F2 on sheet 4 had the area codes(4G and 5G) as well as the possible site codes(AZ and ZZ). Basically this will allow me to search either the area or the site with one drop down menu.

Listed like this in the drop down menu
4G
AZ
5G
ZZ

Ok perfecting formula as u wish and data validation as well

Awesome that looks like it will sort it out for me, now I just need to spend some time to understand the formula. How do you put { } on the start and end of the formula? I can't manually enter it, what impact do they have on the formula?

That's sign for array formula not to ENTER manually, it will comes up when you press CTRL-SHIFT-ENTER button together, ENTER alone is not sufficient....

Is there no way of searching the two columns A and B in Sheets 1 to identify the look up value either 4G,5G,AZ,ZZ the reason I ask is because there will not always be a 'G' in the area, it could be varied but will never have the exact same letters and numbers. If I could look up the two columns it would make it much easier for me, in the report i'm going to build there could be 20 different codes in Area(column A) and even more codes in Site(column B).

Change formula to:

=IFERROR(INDEX(Sheet1!$F$2:$F$29,MATCH(1,(Sheet1!$A$2:$A$29=$C$2)*(Sheet1!$C$2:$C$29=$B5)*(Sheet1!$E$2:$E$29=C$4),0)),INDEX(Sheet1!$F$2:$F$29,MATCH(1,(Sheet1!$B$2:$B$29=$C$2)*(Sheet1!$C$2:$C$29=$B5)*(Sheet1!$E$2:$E$29=C$4),0)))

copied and press CTRL-SHIFT-ENTER button together

and drag down and cross

Thanks Azumi that is exactly what I am looking for. Really appreciate your help.

Yes you're welcome, happy to help