VLOOKUP formula error

[phmalu]

Hi all,

Can you guys try to figure out what's wrong with my formula? Maybe VLOOKUP is not the best function in my case once the desired result will always be in column A but I really don't know what else to try.

=IF(H4=B3;VLOOKUP(I4;A4:D9;2;FALSE);IF(H4=C3;VLOOKUP(I4;A4:D9;3;FALSE);IF(H4=D3;VLOOKUP(I4;A4:D9;4;FALSE))))

Thanks to all!

 

[AhoyNC]

VLOOKUP can't return values to the left.
Try:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1
2
3	Reading	1000	2000	3000
4	100	0.00	0.00	0.00				1000	2	150
5	150	2.00	3.00	5.00
6	200	2.10	3.10	5.10
7	250	4.00	6.00	10.00
8	300	4.10	6.10	10.10
9	350	6.00	9.00	15.00
Sheet

 

[Aladin Akyurek]

The category values appears to be sorted in ascending order. If we need to return 150, also for 2.05 while category = 1000, an approximate match is required:

Either...
=INDEX($A$4:$A$9,MATCH(I4,INDEX($B$4:$D$9,0,MATCH($H$4,$B$3:$D$3,0)),1))

Or…
=LOOKUP(I4,INDEX($B$4:$D$9,0,MATCH(H4,$B$3:$D$3,0)),$A$4:$A$9)

 

[rlv01]

In your case, INDEX-MATCH might be a better fit.

=IF(H4=B3,INDEX(A4:A9,MATCH(I4,B4:B9,0)),IF(H4=C3,INDEX(A4:A9,MATCH(I4,C4:C9,0)),IF(H4=D3,INDEX(A4:A9,MATCH(I4,D4:D9,0)),"No Result")))

 

[phmalu]

VLOOKUP can't return values to the left.

Try:

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 23px;"><col style="width: 22px;"><col style="width: 16px;"><col style="width: 64px;"><col style="width: 51px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]Reading[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"]2000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"]3000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]1000[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]150[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]5.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2.10[/TD]
[TD="align: right"]3.10[/TD]
[TD="align: right"]5.10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]10.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]6.10[/TD]
[TD="align: right"]10.10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]15.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula J4 =INDEX($A$4:$A$9,MATCH(I4,INDEX($B$4:$D$9,0,MATCH($H$4,$B$3:$D$3,0)),0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

It worked but the final result was simply "TRUE" not the value on the "Reading" column .

 

[Aladin Akyurek]

It worked but the final result was simply "TRUE" not the value on the "Reading" column .

What about Category = 1000 and Value = 2,05?

 

[phmalu]

What about Category = 1000 and Value = 2,05?

Intermediary values are not important in my case, the raw data will be converted first so it fits the exact pre-defined values.
Btw, this is just a hypothetical example I came across, not the real data I'm using. But the main problem now is that all the possible formulas you guys posted are simply returning "TRUE". None of them actually worked and returned the value on the "Reading" column. And I really don't know why .

 

[phmalu]

Intermediary values are not important in my case, the raw data will be converted first so it fits the exact pre-defined values.
Btw, this is just a hypothetical example I came across, not the real data I'm using. But the main problem now is that all the possible formulas you guys posted are simply returning "TRUE". None of them actually worked and returned the value on the "Reading" column. And I really don't know why .

Nvm, not sure what was going on earlier but I was able to make it work this time. Apparently the combination of Index + match is very powerful so I'm going to research a bit about it and learn how to set up those formulas by my own. I'm pretty sure I'll be needing them in the near future.

Thank you very much!!

 

[Aladin Akyurek]

Intermediary values are not important in my case, the raw data will be converted first so it fits the exact pre-defined values.
Btw, this is just a hypothetical example I came across, not the real data I'm using. But the main problem now is that all the possible formulas you guys posted are simply returning "TRUE". None of them actually worked and returned the value on the "Reading" column. And I really don't know why .

AhoyNC suggested a formula that does exact match and returns the corresponding Reading value if that exists:

=INDEX($A$4:$A$9,MATCH(I4,INDEX($B$4:$D$9,0,MATCH($H$4,$B$3:$D$3,0)),0))

 

[AhoyNC]

You may find this link on INDEX / MATCH to be of interest.

https://www.youtube.com/watch?v=u-8D2G0y9EM