Vlookup - Two lookupvalues - complex

[cpsnuggle]

Hi,

I have below table of which I need to apply the Vlookup function to get my input right.

I have my Table_Array which contains Code, Name, Number & Currency. I need my function to be able to distinguish between numbers. E.g. If I type 100 in lookup value it would just return the first line which matches 100. But as seen in the table_array, there are different numbers for the same code. So what I essentially need is the function to call the written number, when I manually type in the code (Lookupvalue_1) and the currency (lookupvalue_2)

Thanks in advance

 

[arthurbr]

Supposing your table starts in col F try

=sumproduct(--(F3:F9=A3),--(I3:I9=D3)*H3:H9)

for number
For name

=INDEX(F3:I9,sumproduct(--(f3:f9=a3),--(h3:h9=d3),row(f3:i9))-row(f3:i9+1)

 

[cpsnuggle]

Supposing your table starts in col F try

=sumproduct(--(F3:F9=A3),--(I3:I9=D3)*H3:H9)

for number
For name

=INDEX(F3:I9,sumproduct(--(f3:f9=a3),--(h3:h9=d3),row(f3:i9))-row(f3:i9+1)

Does this look rights?

=INDEX(G36:J42;sumproduct((G36:G42=A3);(J36:J42=E36);row(G36:J42))-row(G36:J42+1))

Based on this table:

 

[arthurbr]

Like this?

=INDEX($G$36:$J$42;sumproduct(($G$36:$G$42=$B$36);($J$36:$J4$2=$E$36);row(G36:J42))-row(G36:J42)+1)

Use commas or semi colons according to your regional settings

 

[cpsnuggle]

Like this?

=INDEX($G$36:$J$42;sumproduct(($G$36:$G$42=$B$36);($J$36:$J4$2=$E$36);row(G36:J42))-row(G36:J42)+1)

Use commas or semi colons according to your regional settings

It doesn't seem to work, when I write "100" as code and "USD" as currency

 

[arthurbr]

Typo sorry
=INDEX($G$36:$J$42;sumproduct(($G$36:$G$42=$B$36);($J$36:$J$42=$E$36);row(G36:J42))-row(G36:J42)+1)

 

[cpsnuggle]

Typo sorry
=INDEX($G$36:$J$42;sumproduct(($G$36:$G$42=$B$36);($J$36:$J$42=$E$36);row(G36:J42))-row(G36:J42)+1

Hmmm... still doesn't work..

 

[Alex Blakenburg]

Your profile says you have 365 which gives you quite a number of additional options.

Xlookup

20210810 XLookup Filter Lookup.xlsx
	A	B	C	D	E	F
2		Lookupvalue_1	Name	Number	Lookupvalue_2
3		100	A	10363	USD
4		90	B	10680	USD
5		130	C	10116	USD
6
XLookup

Cell Formulas
Range	Formula
C3:D5	C3	=XLOOKUP(1,($G$3:$G$9=$B3)*($J$3:$J$9=$E3),H$3:H$9)

Filter

20210810 XLookup Filter Lookup.xlsx
	A	B	C	D	E	F
2		Lookupvalue_1	Name	Number	Lookupvalue_2
3		100	A	10363	USD
4		90	B	10680	USD
5		130	C	10116	USD
6
Filter

Cell Formulas
Range	Formula
C3:D5	C3	=FILTER($H$3:$I$9,($G$3:$G$9=$B3)*($J$3:$J$9=$E3),"Not Found")
Dynamic array formulas.

 

[cpsnuggle]

When applying both of the above, I get #N/A (Xlookup) and "Not Found" (Filter). Any suggestions?

 

[Alex Blakenburg]

Try this:

• Click on the copy symbol on the XL2BB below
• Click on B34 of your spreadsheet (assuming the picture you gave of the row / column position in Post #3 & #5 is accurate)
• And Paste

20210810 XLookup Filter Lookup.xlsx
	B	C	D	E	F
34	Input
35	Lookupvalue_1	Name	Number	Lookupvalue_2
36	100	A	10363	USD
37	90	B	10680	USD
38	130	C	10116	USD
39
XLookup

Cell Formulas
Range	Formula
C36:D38	C36	=XLOOKUP(1,($G$36:$G$42=$B36)*($J$36:$J$42=$E36),H$36:H$42)