Formula with CHOOSE functio in three table same size

GerryZ · Apr 25, 2015

hello Everbody
in H2 I made a formula with INDIRECT that gives me back the right result choosing in different tables with the same size.
But now I'm stuck because I'd like to make a similar formula with CHOOSE function
I know is possible but I'm really stuck
Thank you for yor Help!

Book1

A

B

C

D

E

F

G

H

1

Tabella1

A

B

C

D

criteria1

Tabella1

2

1

zac

fux

tor

zor

riga

2

3

2

mac

dax

mel

roz

colonna

D

4

3

fra

ann

mar

ras

result

roz

5

4

joe

lui

tin

aff

6

7

8

Tabella2

A

B

C

D

9

1

non

nik

chi

sas

10

2

nul

min

per

feb

11

3

pro

giu

sba

chi

12

4

nie

poc

man

tet

13

14

15

Tabella3

A

B

C

D

16

1

arr

bra

ita

sfi

17

2

inc

ner

com

una

18

3

bes

for

per

mia

19

4

rot

cog

eca

soc

Sheet1

Cell Formulas
Range	Formula
H4	=INDEX(INDIRECT(H1),MATCH(H2,righe,0),MATCH(H3,colonne,0))

Named Ranges
Name	Refers To	Cells
colonne	=Sheet1!$B$1:$E$1
righe	=Sheet1!$A$2:$A$5

Domenic · Apr 25, 2015

Try...

Code:

=INDEX(CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$2:$E$5,$B$9:$E$12,$B$16:$E$19),MATCH(H$2,CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$A$2:$A$5,$A$9:$A$12,$A$16:$A$19),0),MATCH(H$3,CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$1:$E$1,$B$8:$E$8,$B$15:$E$15),0))

If the row and column headers are the same for each table, as per your example, try...

Code:

=INDEX(CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$2:$E$5,$B$9:$E$12,$B$16:$E$19),MATCH(H$2,$A$2:$A$5,0),MATCH(H$3,$B$1:$E$1,0))

Hope this helps!

GerryZ · Apr 25, 2015

Domenic said:

Try...

Code:

=INDEX(CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$2:$E$5,$B$9:$E$12,$B$16:$E$19),MATCH(H$2,CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$A$2:$A$5,$A$9:$A$12,$A$16:$A$19),0),MATCH(H$3,CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$1:$E$1,$B$8:$E$8,$B$15:$E$15),0))

If the row and column headers are the same for each table, as per your example, try...

Code:

=INDEX(CHOOSE(MATCH(H$1,{"Tabella1","Tabella2","Tabella3"},0),$B$2:$E$5,$B$9:$E$12,$B$16:$E$19),MATCH(H$2,$A$2:$A$5,0),MATCH(H$3,$B$1:$E$1,0))

Hope this helps!

Excelent Domenic!!
I will use the second formula
Anyway next thread i'll need the same formula with INDIRECT function and with CHOOSE function, but with tables with DIFFERENT size

chicagocomputerclasses · Apr 25, 2015

Hi GerryZ,

If you check this thread started by you a while back
http://www.mrexcel.com/forum/excel-questions/842957-lookup-3-tables-different-size.html

On my latest post I came up with a solution that will lookup any size tables and any quantity of tables for this.

GerryZ · Apr 25, 2015

chicagocomputerclasses said:
Hi GerryZ,

If you check this thread started by you a while back
http://www.mrexcel.com/forum/excel-questions/842957-lookup-3-tables-different-size.html

On my latest post I came up with a solution that will lookup any size tables and any quantity of tables for this.

Yesss!!
Thank you Chicagocomp...I Knew i did it before but I was lost in my 100s of file a 100s of old threads, but now I cannot forget this formula anymore
Now i'm prepering a new Thread and I will compare your formula with CHOOSE with a formula with INDIRECT function
because this time I'm really interested to learn how to search a value in different Tables with differents formulas
Thank You

Rick Rothstein · Apr 25, 2015

GerryZ said:
Worksheet Formulas

Cell Formula
H4 =INDEX(INDIRECT(H1),MATCH(H2,righe,0),MATCH(H3,colonne,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

I know you asked for a function using CHOOSE, but I wanted to point out this formula, which appears to work, might be better than the above one given that INDIRECT is a Volatile function (not sure what the equivalent function names would be in Italian)...

=INDEX(A:E,MATCH(H1,A:A,0)+H2,CODE(H3)-63)

GerryZ · Apr 25, 2015

@Rick
I just tried your formula but doesn't work error #RIF
Any way is difficult to understand your formula even if is short one
for the moment I'will use Domenic Formulaùthank you!

Rick Rothstein · Apr 25, 2015

GerryZ said:
@Rick
I just tried your formula but doesn't work error #RIF

Did you type the letter in cell H3 in lower case by any chance? As written, the code is expecting the letter to be upper case... if you need to accept lower case letters, then the formula would need to be this...

=INDEX(A:E,MATCH(H1,A:A,0)+H2,CODE(UPPER(H3))-63)

chicagocomputerclasses · Apr 25, 2015

Rick Rothstein ,

GerryZ always tricks you with his sample data by including numbers and letters in columns, but his real data is actual data, not just incremental numbers and letters as he has in his sample table.

I've fallen into that trap before.

Rick Rothstein · Apr 25, 2015

chicagocomputerclasses said:
Rick Rothstein ,

GerryZ always tricks you with his sample data by including numbers and letters in columns, but his real data is actual data, not just incremental numbers and letters as he has in his sample table. I've fallen into that trap before.

I don't think that is the case here... I am pretty sure "riga" means row, and "colonna" means column. I believe the problem is as I described in Message #8.

Formula with CHOOSE functio in three table same size

GerryZ

Well-known Member

Excel Facts

Domenic

MrExcel MVP

GerryZ

Well-known Member

chicagocomputerclasses

Well-known Member

GerryZ

Well-known Member

Rick Rothstein

MrExcel MVP

GerryZ

Well-known Member

Rick Rothstein

MrExcel MVP

chicagocomputerclasses

Well-known Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Formula with CHOOSE functio in three table same size

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock