If value found in range A then return value from same row but range B
I struggle to put up a formula that I want the following to happen:
- Formula in Cell B1: If the value of cell A1 is located in range F1:F100, then return value from same row it was found from the range F1:F100, but from range G1:G100 instead
- Example: Input in A1 is 58. 58 exist in range F1:F100 and appeared in cell F7. The value of cell G7 is 58,4. The formula should then return 58,4.
- If value in A1 is not found in range F1:F100, then A1 * 1,02.
I was thinking about doing a mix of IF and VLOOKUP, like IF VLOOKUP value found then VLOOKUP same value but from column 2 instead else use value *1.02, but can't get it to work properly.
Any suggestions?
Posted Mar 6, 2019, 3:03 PM UTC
111
Try this
To explain
=INDEX(myRange,5)This will return the value of 5th cell in the range myRange
=MATCH(myRange,"This",0)This will return the point at which the word "This" is found in myRange, 0 indicated an exact match needed. If the 2nd cell contains the word This, the function will return 2.
=IFERROR(myFunction, 2+2)Is the result of myFunction will result in an error, default to this value, 2+2 in this case
Combine them together for the function above
EDIT: Updates
This worked perfectly!
...but here comes the next challenge, where I want to expand from above!
"(B1-(C1*D1))/1,2" more or less
I tried making formulas for this, but I get the error "there are one or more circular references where a formula refers to its own cell either directly or indirectly"
Any workaround for this?
D1
E1
Sorry for the late reply.
Both formulas work great, but I have one last challenge.
I want to edit the B1 formula regarding the third thing I want to occur.
Before I wrote:
" If value in A1 is not found in range F1:F100, then A1 * 1,02."
Now I want it to change to to the following:
" If value in A1 is not found in range F1:F100, then D1 multiply with C1, plus (E1 multiply with 1,02)"
This causes another "there are one or more circular references where a formula refers to its own cell either directly or indirectly" error.
Is there an easy way to fix?
There is no way to fix this. D1 and E1 specifically refer to B1, if B1 is referring to D1 there it no starting point for a value.
D1 =IF(B1<C1,0,ROUNDDOWN(B1/C1,0))
You would need to replace the value B1 here with something else.
Did you get it to work? :)