Using VLOOKUP across several arrays to return text

[BasselJamali]

Hi all,

I am creating a database to search through medical journals. The way I have it organized is that I cannot use VLOOKUP to go through the list down vertically, and I must use multiple table arrays. As a work-around, I found this thread which shows exactly what I want to do, but the second solution to this problem doesn't work for returning the titles of the cases I wish to display. It seems to only work with numbers. Anybody have a solution for me? I would appreciate it greatly.

 

[Domenic]

Probably best you post a small sample of your data, along with your expected results, and someone will be able to help.

 

[BasselJamali]

So here is my data:
[table="width: 500, class: grid"]
[tr]
[td]Year[/td]
[td]Rank[/td]
[td]Search[/td]
[td]2009[/td]
[td]Rank[/td]
[td]Search[/td]
[td]2010[/td]
[/tr]
[tr]
[td]01[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]02[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]03[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]04[/td]
[td]1[/td]
[td]6.00006[/td]
[td]Adenocarcinoma[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]05[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]06[/td]
[td]5[/td]
[td]33.0000008[/td]
[td]High Grade endometrioid uterine carcinoma[/td]
[td]4[/td]
[td]24.000001[/td]
[td]Chromophobe renal cell carcinoma[/td]
[/tr]
[tr]
[td]07[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]08[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]09[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[td][/td]
[td][/td]
[td]Case name[/td]
[/tr]
[tr]
[td]10[/td]
[td]3[/td]
[td]22.00012[/td]
[td]Papillary renal cell carcinoma[/td]
[td]2[/td]
[td]12.00001[/td]
[td]Renal cell carcinoma[/td]
[/tr]
[tr]
[td]Search:[/td]
[td]carcinoma[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]1[/td]
[td]Adenocarcinoma[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]Papillary renal cell carcinoma[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]High grade endometrioid uterine carcinoma[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

Basically what I am attempting to do is use VLOOKUP across both the 2009 table array and 2010, and for more years after that. As you can see, I can get 1,3, and 5 to return because I am using VLOOKUP to search the first table array to return names matching that ranking. Notice that number 2 and 4 under the search is empty, even though they are ranked under 2010. This is because I cannot get VLOOKUP to look at both table arrays. I realize that I'm asking a lot and I would really appreciate any help you could give. Thanks.

 

[Domenic]

The the following formula is based on your sample data, and it assume that it starts at A1...

B13, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$2:$G$11,SMALL(IF(MOD(COLUMN($B$2:$G$11)-COLUMN($B$2),3)=0,IF($B$2:$G$11=$A13,ROW($B$2:$G$11)-ROW($B$2)+1)),1),MATCH($A13,IF(MOD(COLUMN($B$2:$G$11)-COLUMN($B$2),3)=0,INDEX($B$2:$G$11,SMALL(IF(MOD(COLUMN($B$2:$G$11)-COLUMN($B$2),3)=0,IF($B$2:$G$11=$A13,ROW($B$2:$G$11)-ROW($B$2)+1)),1),0)),0)+2)

Adjust the range to include your other years, accordingly.

Hope this helps!