xlookup against multiple columns in lookup array

[RookieExcel22]

Hi,

I have the below table of data

Column G	Col H	Col I	Col J
Value	Value	Question 1	Question 2
AB	AD	3	2
AC		4	5

I want to bring the data in column Question 1 into a table that looks like this

Col A	Col B
Value	Question 1
AB	3
AC	4
AD	3

I know I can use an ifna(vlookup but that is a lot of effort. I tried an xlookup hoping it could look against both value columns but that did not work. I ended up using this formula:

=IFNA(XLOOKUP(A2,$G$2:$G$3,XLOOKUP(B$1,$I$1:$J$1,$I$2:$J$3)),XLOOKUP(A2,$H$2:$H$3,XLOOKUP(B$1,$I$1:$J$1,$I$2:$J$3)))

While it did work, it is a bit of effort to set up as I have 15 value columns. Wondering if there is a simpler way! Thank you.

 

[Kerryx]

power query

Book1
	A	B	C	D	E	F	G
1	Column G	Col H	Col I	ColumnJ	Value.1	Question 1	Question 2
2	Value	Value	Question 1	Question 2	AB	3	2
3	AB	AD	3	2	AC	4	5
4	AC		4	5	AD	3	2
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column G", type text}, {"Col H", type text}, {"Col I", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", type text}, {"Value_1", type text}, {"Question 1", Int64.Type}, {"Question 2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Question 1", "Question 2"}, "Attribute", "Value.1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Attribute", "Value.1", "Question 1", "Question 2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value.1", Order.Ascending}})
in
    #"Sorted Rows"

 

[RookieExcel22]

power query

Book1
	A	B	C	D	E	F	G
1	Column G	Col H	Col I	ColumnJ	Value.1	Question 1	Question 2
2	Value	Value	Question 1	Question 2	AB	3	2
3	AB	AD	3	2	AC	4	5
4	AC		4	5	AD	3	2
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column G", type text}, {"Col H", type text}, {"Col I", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", type text}, {"Value_1", type text}, {"Question 1", Int64.Type}, {"Question 2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Question 1", "Question 2"}, "Attribute", "Value.1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Attribute", "Value.1", "Question 1", "Question 2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value.1", Order.Ascending}})
in
    #"Sorted Rows"

Ah not familiar with power query. I will have to do some research! Thank you.

 

[Kerryx]

Here is some info
VBA to transpose blocks (arrays) of data by

@alansidman​

​

 

[RookieExcel22]

Here is some info
VBA to transpose blocks (arrays) of data by

@alansidman​

​

Very cool. Thank you for your help.

 

[Fluff]

A formula option

Fluff.xlsm
	G	H	I	J	K	L	M
1	Value	Value	Question 1	Question 2			Question 1
2	AB	AD	3	2		AB	3
3	AC		4	5		AD	3
4						AC	4
5						AF
Master

Cell Formulas
Range	Formula
M2:M5	M2	=XLOOKUP(L2,TOCOL($G$2:$H$3,1,1),TOCOL(IF($G$2:$H$3<>"",CHOOSECOLS($I$2:$J$3,XMATCH(M$1,$I$1:$J$1)),1/0),2,1),"")

 

[RookieExcel22]

A formula option

Fluff.xlsm
	G	H	I	J	K	L	M
1	Value	Value	Question 1	Question 2			Question 1
2	AB	AD	3	2		AB	3
3	AC		4	5		AD	3
4						AC	4
5						AF
Master

Cell Formulas
Range	Formula
M2:M5	M2	=XLOOKUP(L2,TOCOL($G$2:$H$3,1,1),TOCOL(IF($G$2:$H$3<>"",CHOOSECOLS($I$2:$J$3,XMATCH(M$1,$I$1:$J$1)),1/0),2,1),"")

Awesome this worked. Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RookieExcel22]

A formula option

Fluff.xlsm
	G	H	I	J	K	L	M
1	Value	Value	Question 1	Question 2			Question 1
2	AB	AD	3	2		AB	3
3	AC		4	5		AD	3
4						AC	4
5						AF
Master

Cell Formulas
Range	Formula
M2:M5	M2	=XLOOKUP(L2,TOCOL($G$2:$H$3,1,1),TOCOL(IF($G$2:$H$3<>"",CHOOSECOLS($I$2:$J$3,XMATCH(M$1,$I$1:$J$1)),1/0),2,1),"")

Hi, does this have a max character return count? I get a #calc error on some of my columns. I noticed if I remove characters to a max of 255 the #calc disappears. Anything over 255 and it comes back? Thanks!

 

[Fluff]

It works for me with more than 255 characters. Can you post the actual data.