Simple formula

[LebD]

Hi
I am trying to have this formula working with one more criteria. Not sure why I am stuck:, initially this is working
=IFERROR(IF(LEN(VLOOKUP(C4;'Sheet2'!H:BD;5;FALSE))=0;0;VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100);0)

But I need to add one more criteria, if vlookup returns >100, if true it should show 100 otherwise the rest of the formula above.

it is one more IF criteria to be inserted in the 3rd part of the formula above, theoritically like that but its not working

=IFERROR(IF(LEN(VLOOKUP(C4;'Sheet2'!H:BD;5;FALSE))=0;0;IF(VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100>100;100;VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100);0)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=MIN(XLOOKUP(C4,Sheet2!H:H,Sheet2!L:L,0,0)/100,100)

 

[LebD]

Thank you

But your formula returning 0 every time?‍

 

[LebD]

Nope sorry. It returns the values. however, some nbs are returned N/A and those who are over 100% are returned as is (like if it is 104%, it returns 104%), I need it max 100%

 

[LebD]

the instances where it returns N/A is when the source has an X (not a nb)

 

[ravi2628]

Please Try This.

Book2
	A	B	C	D	E
1	Test1	77%		Test2	Previous Formula
2	Test2	69%		69%
3	Test3	77%		Test4	With New Criteria
4	Test4	105%		100%
5	Test5	26%
6	Test6	27%
7	Test7	99%
8	Test8	96%
9	Test9	67%
10	Test10	76%
11	Test11	78%
12	Test12	27%
13	Test13	39%
14	Test14	98%
15	Test15	61%
16	Test16	8%
17	Test17	67%
18	Test18	37%
19	Test19	53%
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IF(VLOOKUP(D1,A1:B19,2,0)=0,"0",VLOOKUP(D1,A1:B19,2,0))
D4	D4	=IF(VLOOKUP(D3,A1:B19,2,0)=0,"0",IF(VLOOKUP(D3,A1:B19,2,0)>1,"100%",VLOOKUP(D3,A1:B19,2,0)))

 

[Alex Blakenburg]

The only way I can get an error with @Fluff 's formula is if the return value is text from the value column (L).
You can just wrap it in an IFERROR statement.

=IFERROR(MIN(XLOOKUP(C4;Sheet2!H:H;Sheet2!L:L;0;0)/100;100);0)

 

[LebD]

I still have N/A but probably with an IFError it can be removed.
But the main issue now, is that if the return data is between 1 and 100, like lets say 70, it shows 100

 

[LebD]

Yes its working, with an error it can fix it. But when the return data is over 100, it does not give me 100, but whatever the data is (108 = 108 for example)

 

[Alex Blakenburg]

You need to give us an exact example.