I can't use xlookup at work

[michael2455]

Basically I'm referencing 2 separate tables and I'm trying not to rewrite the tables. I'm using the following function:

=IF($E2="","",IF(B2="M",VLOOKUP(E2,'SHEET'!A3:K48,LOOKUP(C2,{17,22,27,32,37,42,47,52,57,62},{2,3,4,5,6,7,8,9,10,11})),VLOOKUP(E2,'SHEET'!N3:X48,LOOKUP(C2,{17,22,27,32,37,42,47,52,57,62},{2,3,4,5,6,7,8,9,10,11}))))

This puts me where I want on either table but now I need it to return the data from the first column in the table for the row I'm on. Any ideas on what to add to this function? The data is in ascending order in the columns but not the rows.

[#2]

@michael2455 I dumped your formula in A1 on a sheet with some dummy data to see what you are trying to do. Let's say B2 contains M, C2 contains 22 and E2 contains 4. That returns the value of cell C6 (the red cell), being the 3rd cell on the 4th row in the first table. Obviously, the first column on that row contains the lookup value you have E2, being 4  in cell A6. See picture.

This is probably not what you are asking for, so perhaps you can clarify your intentions. Upload the file or share it via OneDrive or something similar.

[#3]

Yes that's what I'm getting. From here I need what is on Column A in this row.

[#4]

@michael2455 But that's the value you have in E2. But perhaps I'm totally missing your point.

[#5]

@Riny_van_Eekelen E2 is where I want to type my data. K2 is where I put that formula. Right now E2 and K2 are reversed. I'm trying to get the data from the first column of each table based on each persons age and the score they received from the table (E2) into K2.

[#6]

@michael2455 Sorry, don't follow. Which values would you want to return where and with what logic?

[#7]

My formula is looking for E2, by checking to match the age of the individual and the gender. The left table is for men and the right is for women. K2 is currently finding that in either table, but what I really need K2 to do is to get the score A:A and N:N based on what it just found.

[#8]

@michael2455 Still don't get it. Sorry! Can't you just say exactly what you want to see where? So, for Person A, you get a score of 330 in K2 based on the 99 in E2. What more do you want to return where?

[#9]

@Riny_van_Eekelen I want to type 330 in E2 and receive 99 in K2 based on the correct column associated with the person's age.

[#10]

@michael2455 OK. Can you upload or share your file? That will make it a lot easier.

[#11]

@michael2455 Perhaps the attached file will help you on your way. Not exactly the same but very similar. Note that I used named ranges to avoid confusing cell references. The formula is in K2.