Look up with multiple returns

[Tiggereminem]

Hi,

I have a massive dataset (big for me) of just under 10000 lines. I want tbe data to two thing. The first I have achieved, being if a vlookup of airport codes, so put in the 3 digit code eg A1= LHR, and it returns B1= Heathrow C1=London and D1= UK.

What I want it to also do is if we have the four areas, code, airport, city and country; if we enter the criteria under any of these headings it will return the relevant values from data sets. So working from headers left to right, it would be increasing the number of answers. Again example, under city we enter London, the airports work return Gatwick, Luton, Heathrow and Stanstead.

I have looked up excel sites and found answers and formulae but when i execute it always returns an error. I cant understand why.

Hope this is clear.

 

[cyrilbrd]

Hi,

I have a massive dataset (big for me) of just under 10000 lines. I want tbe data to two thing. The first I have achieved, being if a vlookup of airport codes, so put in the 3 digit code eg A1= LHR, and it returns B1= Heathrow C1=London and D1= UK.

What I want it to also do is if we have the four areas, code, airport, city and country; if we enter the criteria under any of these headings it will return the relevant values from data sets. So working from headers left to right, it would be increasing the number of answers. Again example, under city we enter London, the airports work return Gatwick, Luton, Heathrow and Stanstead.

I have looked up excel sites and found answers and formulae but when i execute it always returns an error. I cant understand why.

Hope this is clear.

Maybe a short sample of your data and where you would like the query to be display would help.
Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style> [TABLE="width: 260"]
<colgroup><col style="width:65pt" span="4" width="65"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]code[/TD]
[TD="class: xl63, width: 65"]city[/TD]
[TD="class: xl63, width: 65"]state[/TD]
[TD="class: xl63, width: 65"]country[/TD]
[/TR]
[TR]
[TD="class: xl63"]x1[/TD]
[TD="class: xl63"]a[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]w[/TD]
[/TR]
[TR]
[TD="class: xl63"]x2[/TD]
[TD="class: xl63"]b[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]w[/TD]
[/TR]
[TR]
[TD="class: xl63"]x3[/TD]
[TD="class: xl63"]cd[/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"]w[/TD]
[/TR]
[TR]
[TD="class: xl63"]x4[/TD]
[TD="class: xl63"]e[/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"]w[/TD]
[/TR]
[TR]
[TD="class: xl63"]x5[/TD]
[TD="class: xl63"]f[/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"]w[/TD]
[/TR]
</tbody>[/TABLE]

In E7:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style> [TABLE="width: 130"]
<colgroup><col style="width:65pt" span="2" width="65"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]count[/TD]
[TD="class: xl63, width: 65"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]state[/TD]
[TD="class: xl63"]y[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]cd[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]e[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]f[/TD]
[/TR]
</tbody>[/TABLE]

formula in F7 is =COUNTIF($C$2:$C$6,F8)
formula in F9 is =IF(ROWS(F$9:F9)>(F$7),"",INDEX($B:$B,SMALL(IF(C$2:C$15=$F$8,ROW(C$2:C$15)),ROWS(F$9:F9)))) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.

 

[Tiggereminem]

Thanks Cyribrd, I brought this over to my data set and I also brought in some match functions. I deliberately didn't put my own data on this, as I was thinking, that if someone else had the same problem, the basic example (as you have given), was amazingly simple and worked perfectly. Even more so, I understood the formulae (once you posted it) what it was doing and asking.

I now have a three way working questioning data set. Answers about which airport a code matches, which ones are in which city and also which country.

Thank you again for the help.

 

[cyrilbrd]

Thanks Cyribrd, I brought this over to my data set and I also brought in some match functions. I deliberately didn't put my own data on this, as I was thinking, that if someone else had the same problem, the basic example (as you have given), was amazingly simple and worked perfectly. Even more so, I understood the formulae (once you posted it) what it was doing and asking.

I now have a three way working questioning data set. Answers about which airport a code matches, which ones are in which city and also which country.

Thank you again for the help.

Thanks for the feedback.
You are most welcome.
Glad you got it.