How to lookup a value

[excelos]

Hello

In the below, how can I lookup the first row for a particular letter X and then the second row for another value XX and then return the value below XX ?
E.g. let's say I want to return the value under B and then under BC, i.e. 6.
I would prefer to avoid OFFSET and also not to rely on empty cells as they can be filled.
Last, using INDEX to return the position of the first lookup and then use that to limit the next lookup array is a bit messy.
Would any lookup search mode (next smallest/largest etc) could work with text?

Thanks!

A			B
AA	AB		BB	BC
1​	3​		5​	6​
2​	4​		7​	8​

 

[Peter_SSs]

A lot of members have looked at your thread but no suggestions. The implication is likely that your question is not clear enough.

Perhaps another (slightly larger & slightly different) set of sample data and the expected result together with explaining in a different way might help.

 

[Skybluekid]

How about this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A			B			Table	Column	Row		Table Range
2	AA	AB		BB	BC		B	BC	2		A	1
3	1	3		5	6						B	2
4	2	4		7	8		6				AA	1
5											AB	2
6											BB	1
7											BC	2
8
Sheet2

Cell Formulas
Range	Formula
G4	G4	=INDEX(CHOOSE(XLOOKUP(G2,K2:K7,L2:L7),A,B),I2,XLOOKUP(G2,K2:K7,L2:L7))

Named Ranges
Name	Refers To	Cells
A	=Sheet2!$A$2:$B$4	G4
B	=Sheet2!$D$2:$E$4	G4

 

[excelos]

Thanks, I am pasting again below (unfortunately I cannot install anything here, if it was possible to insert the excel in the webpage, it would have been great):

	Table1			Table2			Table3			Table4
Row1	Col1	Col2		Col1	Col2		Col1	Col2		Col1	Col2
Row2	6​	74​		50​	62​		13​	41​		33​	77​
Row3	99​	90​		49​	77​		71​	21​		45​	31​

I want to lookup a specific Table then a specific Col and Row.
I cannot rely on gaps, empty cells or anything else, I only need to lookup Table names in 1:1, Row names in A:A and Col names in 2:2 but they have to correspond to the correct Table obviously.

In the past, I used to limit the lookup array by using INDEX as part of a cell range but this makes looks quite messy in the formula and difficult to interpret.

OFFSET also is not an option due to volatility.

Any idea?