Multiple VLOOKUP stump

[Sean15]

Hi:

Could you tell me why this formula is returning ""

=IF('LReports'!J3="UG",VLOOKUP(H3,'AVERAGE Price'!$B$5:$D$39,3,0),IF(J3="DF",VLOOKUP('LReports'!H3,'AVERAGE Price'!$B$5:$D$39,2,0),""))

VLOOKUP range looks something like this:

column B column C column D
MMYY DF UG
917 1.8954 1.7754
1017 1.8804 1.6722
1117 1.9728 1.7295

values in J are "DF", "UG" and No code

values in H: 917, 1017, 1117...


Thanks for your help.

Sean

 

[Norie]

What's the value in J3 on 'LReports' and the value in J3 on the sheet the formula is in?

 

[Sean15]

HI:

Formula is in Q3 of 'LReports'

value in J3 = No code

Regards

 

[sunilbsrv2k]

HI:

Formula is in Q3 of 'LReports'

value in J3 = No code

Regards

That's the reason you are getting " ".

As per your code, it would return value from column D if J3 = "DF" ; from column C if J3 = "UG" and " " if J3 has another value

 

[Sean15]

I was expecting "" for values in J3 other than "DF" and "UG" but formula is returning "" for all values in column J.

I don't understand why? Cold you revise formula?

 

[Aladin Akyurek]

=IF('LReports'!J3="UG",VLOOKUP(H3,'AVERAGE Price'!$B$5:$D$39,3,0),IF(J3="DF",VLOOKUP('LReports'!H3,'AVERAGE Price'!$B$5:$D$39,2,0),""))


In K3 of LReports enter:

=IFERROR(VLOOKUP(H3,

'AVERAGE Price'!$B$5:$D$39,VLOOKUP(J3,{"DF",2;"UG",3},2,0),0),"")​

_{^{<strike>
</strike>}}​

 

[Sean15]

@Aladin Akyurek: formula is returning "" for all values.

In VLOOKUP(J3,{"DF",2;"UG",3},2,0),0),""):

where is excel looking up "DF" & UG"

What is 2,0 doing?

Just trying to understand all this.

Average price is on separate worksheet.

All other lookup information is on another worksheet.

Sean

 

[Aladin Akyurek]

The

VLOOKUP(J3,{"DF",2;"UG",3},2,0)

bit is looking up J3 in the had-coded table, which is exactly the same as:

DF, 2
UG, 3

What does

=VLOOKUP(H3,'AVERAGE Price'!$B$5:$D$39,VLOOKUP(J3,{"DF",2;"UG",3},2,0),0)
​

return?

​

 

[Sean15]

Here is sample:

Excel 2010
	H	I	J	K	L
3	1015	2017/10	No price		required value
4	1017	2017/10	UG	1.3845	required value
5	1017	2017/10	DF		should be 1.4551
6	1016	2016/10	UG		should be 1.2731
7	0416	2016/04	DF		should be 1.0566
8	1015	2016/10	UG	1.4875	required value
9	1017	2017/10	DF		should be 1.4551
LReport

Cell Formulas
Range	Formula
K3	=IFERROR(VLOOKUP(H3,pricing!$B$5:$D$9,VLOOKUP(J3,{"DF",2;"UG",3},2,0),0),"")
K4	=IFERROR(VLOOKUP(H4,pricing!$B$5:$D$9,VLOOKUP(J4,{"DF",2;"UG",3},2,0),0),"")
K5	=IFERROR(VLOOKUP(H5,pricing!$B$5:$D$9,VLOOKUP(J5,{"DF",2;"UG",3},2,0),0),"")
K6	=IFERROR(VLOOKUP(H6,pricing!$B$5:$D$9,VLOOKUP(J6,{"DF",2;"UG",3},2,0),0),"")
K7	=IFERROR(VLOOKUP(H7,pricing!$B$5:$D$9,VLOOKUP(J7,{"DF",2;"UG",3},2,0),0),"")
K8	=IFERROR(VLOOKUP(H8,pricing!$B$5:$D$9,VLOOKUP(J8,{"DF",2;"UG",3},2,0),0),"")
K9	=IFERROR(VLOOKUP(H9,pricing!$B$5:$D$9,VLOOKUP(J9,{"DF",2;"UG",3},2,0),0),"")

Excel 2010
	B	C	D
5	MMYY	DF	UG
6	1015	1.5381	1.4875
7	1017	1.4551	1.3845
8	1016	1.2098	1.2731
9	0416	1.0566	1.1080
pricing

Regards,

 

[Aladin Akyurek]

The DF entries in LReport has trailing spaces.

Either remove those spaces or invoke:

=IFERROR(VLOOKUP(H3,pricing!$B$5:$D$9,VLOOKUP(TRIM(J3),$P$3:$Q$4,2,0),0),"")